Suppose you have multiple disks and databases. You might have a question where do my database files reside? The below script gives the directory of each database file.
(MDF\LDF\NDF) using the sys.master_files and sys.dm_os_volume_stats.
select name, volume_mount_point, logical_volume_name,
file_system_type, physical_name, type_desc, state_desc
FROMsys.master_files ASf
CROSSAPPLY sys.dm_os_volume_stats(f.database_id, f.file_id);