What is the difference of the sys.master_files and sys.database_files? I have about 20 databases in my instance but when I query the sys.master_files I do not receive any rows. Why? When I query sys.database_files I get the information about the database files concerning the current database.
系统的区别是什么?master_files sys.database_files ?我的实例中有大约20个数据库，但是当我查询系统时。master_files我不接收任何行。为什么?当我查询系统。database_files我获得关于当前数据库的数据库文件的信息。
1 个解决方案
#1
6
sys。master_files:
Contains a row per file of a database as stored in the master database. This is a single, system-wide view.
包含存储在主数据库中的每个数据库文件的行。这是一个单一的、系统范围的视图。
sys。database_files:
Contains a row per file of a database as stored in the database itself. This is a per-database view.
包含数据库本身中存储的每一个数据库的行。这是一个每个数据库视图。
So, SELECT * FROM sys.master_files should list the files for each database in the instance whereas SELECT * FROM sys.database_files should list the files for the specific database context.
从sys中选择*。master_files应该列出实例中每个数据库的文件，而从sys中选择*。database_files应该列出特定数据库上下文的文件。
Testing this here (SQL 2K8), it works as per the above?
在这里测试一下(SQL 2K8)，它能像上面那样工作吗?
Update: If you're not seeing rows from sys.master_files, it could be a permissions issue as BOL states:
更新:如果没有看到来自sys的行。master_files，可能是由于BOL声明的权限问题:
The minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.
查看相应行所需的最小权限是创建数据库、修改任何数据库或查看任何定义。
Whereas for sys.database_files just requires membership in the public role.
而对于系统。database_files只需要公共角色的成员资格。
#1
6
sys。master_files:
Contains a row per file of a database as stored in the master database. This is a single, system-wide view.
包含存储在主数据库中的每个数据库文件的行。这是一个单一的、系统范围的视图。
sys。database_files:
Contains a row per file of a database as stored in the database itself. This is a per-database view.
包含数据库本身中存储的每一个数据库的行。这是一个每个数据库视图。
So, SELECT * FROM sys.master_files should list the files for each database in the instance whereas SELECT * FROM sys.database_files should list the files for the specific database context.
从sys中选择*。master_files应该列出实例中每个数据库的文件，而从sys中选择*。database_files应该列出特定数据库上下文的文件。
Testing this here (SQL 2K8), it works as per the above?
在这里测试一下(SQL 2K8)，它能像上面那样工作吗?
Update: If you're not seeing rows from sys.master_files, it could be a permissions issue as BOL states:
更新:如果没有看到来自sys的行。master_files，可能是由于BOL声明的权限问题:
The minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.
查看相应行所需的最小权限是创建数据库、修改任何数据库或查看任何定义。
Whereas for sys.database_files just requires membership in the public role.
而对于系统。database_files只需要公共角色的成员资格。