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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.