Tag: List All Database Files on SQL Server

SELECT 
    db.name AS             [Database Name], 
    mf.name AS             [Logical Name], 
    mf.type_desc AS        [File Type], 
    mf.name AS	           [Logical Name],
    mf.state AS	           [ State ],
    mf.state_desc AS       [ State Desc],
    mf.physical_name AS    [Path],
    CAST((mf.Size*8)/1024/1024 AS DECIMAL(18,1)) AS [Current Size(GB)],
    CASE 
	WHEN mf.is_percent_growth >0 THEN
	     'TRUE'  
	ELSE
	     'FALSE' 
	END AS [Autogrowth],
    CASE 
	WHEN  mf.max_size = 0  THEN  'No growth is allowed'
	WHEN  mf.max_size = -1 THEN  'Unlimited'
	ELSE
             CAST(CAST(mf.max_size as BIGINT)*8/1024/1024 AS VARCHAR(30)) +' MB'  
	END AS [MaximumSize] 
FROM 
     sys.master_files AS mf
INNER JOIN sys.databases AS db 
  ON  db.database_id = mf.database_id
Order by db.name,mf.type_desc;