Get information about filegroup, data file name and path along with size in SQL Server
As a DBA, it happens quite a few times that I have to find total number of file groups available for my database along with the file that particular file group is associated with. I may also need to know the physical path of that database file along with the size of data file.
I can query “Sys.Database_files” and “sys.FileGroups” system table to find this important information about my database.
Here is small yet useful query I used to execute in order to get said information.
SELECT dbfile.name AS DatabaseFileName, dbfile.size/128 AS FileSizeInMB, sysFG.name AS FileGroupName, dbfile.physical_name AS DatabaseFilePath FROM sys.database_files AS dbfile INNER JOIN sys.filegroups AS sysFG ON dbfile.data_space_id = sysFG.data_space_id
Here is the output of above query in my laptop:
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.