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;