How to Find Location of Data and Log Files of all SQL Server Databases

To find location of database files for all databases in a SQL Server 2005 and later instance, we can use the view sys.master_files:

SELECT name AS Logical_File_Name, physical_name AS File_Location
FROM master.sys.master_files

This script provides exact location of all data files of all databases.

If you want to get a list of all data files and transaction log files of a SQL Server instance, and see what databases they belong to:

SELECT d.name AS Database_Name,
m.name AS Logical_File_Name,
m.physical_name AS File_Location
FROM sys.master_files m
INNER JOIN sys.databases d
ON (m.database_id = d.database_id)
ORDER BY 1, 2