Listing information about all database files in SQL Server

sql-server sql-server-2008 tsql database-management
M4N picture M4N · Mar 9, 2012 · Viewed 233.2k times · Source

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

Answer

Mikael Eriksson picture Mikael Eriksson · Mar 9, 2012

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.

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More