List Data Files for All Databases on SQL Server
sys.master_files lists all data files of all databases on current SQL Server instance on the other hand sys.database_files lists only database data files for current database where the query is executed on. Using sys.master_files SQL developer and administrators can easily get the list of all data files for all databases created on a SQL Server instance. But if you used to query sys.database_files system view for database files and data file properties, it will not be enough for getting information about an other database's data files like file size, file name and path, etc.
In past, I used sys.database_files with sp_MSForEachDB stored procedure. You can review my SQL tutorial get a list of SQL Server databases and the database sizes.
Of course, now SQL programmers can succeed the same task by only querying sys.master_files system view easily in a single step. Here is how...
This SQL tutorial shows how to to list information about all data files and log files for all databases created on a SQL Server instance.
Let's start with demonstrating the limited use of sys.database_files
System view sys.database_files returns only the data files (.mdf or .ndf) and log files (.ldf) of the current database where the SQL command is executed on.
use [kodyaz.development]
select * from sys.database_files
As seen in below, the result set contains only data files of current database named "kodyaz.development"
Unfortunately, for some cases SQL developer or the SQL Server database administrator require to get data files for all databases. For example, recently I required to develop a script which will create snapshots for all databases on the SQL Server. So I need to know every data file for each database on that server. So I used sys.master_files view for creating database snapshot one by one for the all databases created on the SQL Server instance.
Here how programmers can query sys.master_files as a system-wide solution.
select * from sys.master_files
And below is the screenshot, showing that the query results from sys.master_files system view successfully returns all information about data files for all databases that the administrators create on that target SQL Server database instance.
SQL programmer can use the database_id column for filtering database files for a specific database like shown in below query.
use [kodyaz.development]
declare @target_database sysname = 'ProductiveDB'
select * from sys.master_files where database_id = DB_ID(@target_database)