Get a list of all your database files

I needed a list of all the database files on a SQL Server instance. Here’s how to get this easily.

First create a temp table to hold the data like so..

SELECT *
INTO #db_files
FROM sys.database_files;

Truncate it so we don’t duplicate nay data.

TRUNCATE TABLE #db_files;

Next we use the sp_Msforeachdb to get our database file information.

EXEC sp_MSforeachdb 'USE ?
		     INSERT INTO #db_files
		     SELECT *
		     FROM sys.database_files';

View the data and tidy up once done.

SELECT *
FROM #db_files
ORDER BY physical_name;
 
DROP TABLE #db_files;

3 Comments

  1. Nick says:

    hi,
    You could probably use

    SELECT *
    FROM sys.master_files

    to get what you need.

  2. Rhys says:

    Doh! Of course.

    Rhys

  3. John says:

    How about

    SELECT d.name, m.name, m.physical_name,m.database_id,m.type_desc,m.size
    FROM sys.master_files m join sysdatabases d on m.database_id = d.dbid
    order by d.name

Leave a Reply