Get the list of files and filegroups – SQL Server

One of the routine database administration tasks that a DBA performs is creating files and filegroups. The following query is quite useful for viewing, analysing, and comprehending the files and filegroups created in the database.

The query will assist in obtaining data regarding files, filegroups, their overall size, and their growth.

SELECT a.NAME FileName,
b.NAME FileGroup,
b.type_desc FileGroupType,
physical_name DataFileLocation,
size / 128 SizeInMB,
state_desc FileState,
growth DataFileGrowth
FROM sys.database_files a
INNER JOIN sys.filegroups b ON a.data_space_id = b.data_space_id

Happy learning!!