Many a times i need to move database files to new drive letter just to avoid existing drive going out of space, to determine which database files a particular drive host, you can make use of catalog view sys.master_files as below query.
SELECT DB_NAME(database_id) [Database Name],
Name [Logical File Name] ,
physical_name [Physical File Name] ,
Case When type_desc = 'ROWS' Then 'Data' Else 'Log' End [type]
FROM sys.master_files
WHERE LEFT(physical_name , 1) = 'C'
You will get result set as below.
Hope this helps…!
