SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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"

data and log files for current database on SQL Server

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 Server sys.master_files to list all data files

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)

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.