It has been a while since I last posted a utility query, and today, to avoid other work I am supposed to be doing, I decided to go ahead and work on another post. Today, I went ahead and worked on a server configuration type query. One query I find I use pretty often is the following one that lists the files in the database. In this blog I will include 3 queries. The first will deal with files and databases, and the second runs in a database to see the files and their filegroups (If there is an easy way to get the filegroups at a server level, I am not sure of it\u2026let me know).<\/p>\n
Database Files, All Databases \u2013 File Level <\/strong>(Download source<\/a>)<\/p>\n It is a pretty simple query, and it returns the following columns. (A value of ‘@TOTAL’<\/a> indicates that the row is a summary row, and some file_types will not report a file size. ):<\/p>\n –Get the files and total size of files for all databases<\/p>\n SELECT –the name of the database –the logical name of the file –the size of the file is stored in # of pages –the physical filename only –thanks to Phillip Kelley from http:\/\/stackoverflow.com\/questions\/1024978\/find-index-of-last-occurrence-of-a-sub-string-using-t-sql<\/a> –the physical filename only –the path of the file only FROM sys.master_files Single Database By Filegroup<\/strong> (Download source<\/a>)<\/p>\n\n
CASE WHEN GROUPING(DB_NAME(database_id)) = 1 THEN ‘@TOTAL’
ELSE DB_NAME(database_id)
END AS database_name ,<\/p>\n
CASE WHEN GROUPING(master_files.name) = 1 THEN ‘@TOTAL’
ELSE master_files.name
END AS database_file_name ,<\/p>\n
SUM(master_files.size * 8.0) AS size_in_kb,
SUM(master_files.size * 8.0) \/ 1024.0 AS size_in_mb,
SUM(master_files.size * 8.0) \/ 1024.0 \/ 1024.0 AS size_in_gb,<\/p>\n
CASE WHEN GROUPING(master_files.name) = 1 THEN ”
ELSE MAX(master_files.type_desc)
END AS file_type ,
–the physical filename only
CASE WHEN GROUPING(master_files.name) = 1 THEN ”
ELSE MAX(UPPER(SUBSTRING(master_files.physical_name, 1, 1)))
END AS filesystem_drive_letter , <\/p>\n
–for the REVERSE code to get the filename and path.<\/p>\n
CASE WHEN GROUPING(master_files.name) = 1 THEN ”
ELSE MAX(REVERSE(LEFT(REVERSE(master_files.physical_name),
CHARINDEX(‘\\’, REVERSE(physical_name)) – 1)))
END AS filesystem_file_name ,<\/p>\n
cASE WHEN GROUPING(master_files.name) = 1 THEN ”
ELSE MAX(REPLACE(master_files.physical_name,
REVERSE(LEFT(REVERSE(master_files.physical_name),
CHARINDEX(‘\\’, REVERSE(physical_name)) – 1)), ”))
END AS filesystem_path<\/p>\n
GROUP BY DB_NAME(database_id) , –the database and filegroup and the file (all of the parts)
master_files.name WITH rollup
ORDER BY database_name, database_file_name<\/p>\n