Today, I received a call from friend asking how he can find out which database object belongs to which filegroup.
Well to find out this information can be achieved by writing a simple query using the following system catalogues: sys.filegroups, sys.indexes, sys.database_files and sys.data_spaces.
For example, here is my version of the query to return this information:
-- The following two queries return information about -- which objects belongs to which filegroup SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName] ,i.[index_id] AS [IndexID] ,i.[name] AS [IndexName] ,i.[type_desc] AS [IndexType] ,i.[data_space_id] AS [DatabaseSpaceID] ,f.[name] AS [FileGroup] ,d.[physical_name] AS [DatabaseFileName] FROM [sys].[indexes] i INNER JOIN [sys].[filegroups] f ON f.[data_space_id] = i.[data_space_id] INNER JOIN [sys].[database_files] d ON f.[data_space_id] = d.[data_space_id] INNER JOIN [sys].[data_spaces] s ON f.[data_space_id] = s.[data_space_id] WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1 ORDER BY OBJECT_NAME(i.[object_id]) ,f.[name] ,i.[data_space_id] GO
See the sample output generated by this query, when I executed against AdventureWork2012 sample database.
I hope you will find the information return by this query useful.