Getting the Paths for All Database Files in a SQL Server Instance

Here's a quick T-SQL tip.

You can get the list of paths for all database files (both data and log files) using the undocumented SQL Server stored procedure sp_MSforeachdb. You can use the below T-SQL statement:

exec sp_MSforeachdb
@command1="USE [?]; SELECT CAST(DB_NAME() AS VARCHAR(50)) AS DBName, CAST(physical_name AS VARCHAR(150)) as Files FROM sys.database_files";

For more info about the undocumented SQL Server stored procedure sp_MSforeachdb, you can check this article on my blog.