Determining Drive letter database files use

Tags

catalog view, drive letter, sys.master_files

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…!