Search code examples
sql-serversql-server-2008t-sqldatabase-management

Listing information about all database files in SQL Server

Is it possible to list information about the files (MDF/LDF) of all databases on an SQL Server?

I'd like to get a list showing which database is using what files on the local disk.

What I tried:

  • exec sp_databases all databases
  • select * from sys.databases shows a lot of information about each database - but unfortunately it doesn't show the files used by each database.
  • select * from sys.database_files shows the mdf/ldf files of the master database - but not the other databases
Solution

  • You can use sys.master_files.

    Contains a row per file of a database as stored in the master database. This is a single, system-wide view.