Tags
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
INNER JOIN
sys.filegroups fg
ON sdf.data_space_id=fg.data_space_id
Thanks,
Subhro Saha
Welcome to the Database World... 
can we get list of tables and their filegroups
Hi Srini,
Please refer the following link..hopefully it will answer your question.. Let me know.
Thanks,
Subhro Saha
Nice query! Thanks ! I added the sp_MSforeachdb with it to get info on all queries on server.
EXEC master.dbo.sp_MSforeachdb
@command1 = ‘USE ? SELECT DB_NAME(),sdf.name AS [FileName],
size/128 AS [Size_in_MB],
fg.name AS [File_Group_Name]
FROM sys.database_files sdf
INNER JOIN
sys.filegroups fg
ON sdf.data_space_id=fg.data_space_id’