Today I am going to write a query that is very basic in nature but for DBAs it is very essential query to get all the necessary details regarding the Filegroups and Files in the database. This information can be gathered from SSMS as well but I prefer this query to get the quick results.
The following query returns the filename, size in MB and the name of the filegroup to which each file belongs:
SELECT sdf.name AS [FileName],
size/128 AS [Size_in_MB],
fg.name AS [File_Group_Name]
FROM sys.database_files sdf