List All Database Files on SQL Server

SELECT AS             [Database Name], AS             [Logical Name], 
    mf.type_desc AS        [File Type], 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)],
	WHEN mf.is_percent_growth >0 THEN
	END AS [Autogrowth],
	WHEN  mf.max_size = 0  THEN  'No growth is allowed'
	WHEN  mf.max_size = -1 THEN  'Unlimited'
             CAST(CAST(mf.max_size as BIGINT)*8/1024/1024 AS VARCHAR(30)) +' MB'  
	END AS [MaximumSize] 
     sys.master_files AS mf
INNER JOIN sys.databases AS db 
  ON  db.database_id = mf.database_id
Order by,mf.type_desc;

Leave a Reply

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

You are commenting using your 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.