Some of the sys tables are great at getting the minimum amount of data needed from them to get to the point. A good example is the sys.master_files, which contains a row per file of each databse on an instance.


SELECT name, physical_name from sys.master_files

However, there’s a lot more info available through that table, and it just requires a bit more effort to get the pertinent info out in a readable format. Below is a query that gives the file name, file path, the growth value of that file, how much it grows by and what it is its maximum size. I’ve added file type and status as they’re good to know plus added a join to the database table to get the database name.


;with MasterFiles( [dbId], [File_Name],[File_Path], Size, MaxSize, Growth, percentageGrowth, [state], [type])
as
(
SELECT database_id, name, physical_name, size, max_size, growth, is_percent_growth, [state], [type]
FROM sys.master_files
)
select
db.[name]
,[File_Name]
,[File_Path]
, 'FileGrowth' =
CASE
WHEN Growth <= 128 AND percentageGrowth = 0 THEN CAST (convert(int,round(Growth*8.000,0)) AS NVARCHAR (256)) + ' KB' WHEN Growth > 128 AND Growth <=131072 AND percentageGrowth = 0 THEN CAST (convert(int,round((Growth*1.000)/128.000,0)) AS NVARCHAR (256)) + ' MB' --CAST (CAST(Size as INT) AS NVARCHAR(256)) + ' kb' WHEN Growth > 131072 AND percentageGrowth = 0 THEN CAST (convert(int,round((Growth*1.000)/(128.000*1024),0)) AS NVARCHAR (256)) + ' GB'
WHEN percentageGrowth = 1 THEN CAST (Growth as nvarchar (256)) + '%'
END
,'FileSize' =
CASE
WHEN Size <= 128 THEN CAST (convert(int,round(Size*8.000,0)) AS NVARCHAR (256)) + ' KB' WHEN Size > 128 AND SIZE <=131072 THEN CAST (convert(int,round((Size*1.000)/128.000,0)) AS NVARCHAR (256)) + ' MB' --CAST (CAST(Size as INT) AS NVARCHAR(256)) + ' kb' WHEN Size > 131072 THEN CAST (convert(int,round((Size*1.000)/(128.000*1024),0)) AS NVARCHAR (256)) + ' GB'
END
, 'Maximum File Size' =
CASE
WHEN MaxSize = -1 THEN 'NO MAX SIZE SET!'
WHEN MaxSize <= 128 THEN CAST (convert(int,round(MaxSize*8.000,0)) AS NVARCHAR (256)) + ' KB' WHEN MaxSize > 128 AND MaxSize <=131072 THEN CAST (convert(int,round((MaxSize*1.000)/128.000,0)) AS NVARCHAR (256)) + ' MB' --CAST (CAST(Size as INT) AS NVARCHAR(256)) + ' kb' WHEN MaxSize > 131072 THEN CAST (convert(int,round((MaxSize*1.000)/(128.000*1024),0)) AS NVARCHAR (256)) + ' GB'
END
, 'File State' =
CASE
WHEN m.[state] =  0 THEN 'ONLINE'
WHEN m.[state] =  1 THEN 'RESTORING'
WHEN m.[state] =  2 THEN 'RECOVERING'
WHEN m.[state] =  3 THEN 'RECOVERY_PENDING'
WHEN m.[state] =  4 THEN 'SUSPECT'
WHEN m.[state] =  5 THEN 'Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.'
WHEN m.[state] =  6 THEN 'OFFLINE'
WHEN m.[state] =  7 THEN 'DEFUNCT'
END
, 'File Type' =
CASE
WHEN [type] = 0 THEN ' Rows. (Includes files of full-text catalogs that are upgraded to or created in SQL Server 2012.)'
WHEN [type] = 1 THEN ' Log'
WHEN [type] = 2 THEN ' FILESTREAM'
WHEN [type] = 3 THEN ' Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.'
WHEN [type] = 4 THEN ' Full-text (Full-text catalogs earlier than SQL Server 2012; full-text catalogs that are upgraded to or created in SQL Server 2012 will report a file type 0.)'
END
from MasterFiles M
INNER JOIN sys.databases db
ON db.database_id = M.[dbId]