List all Objects and Indexes per Filegroup / Partition

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.

ObjectsInFileGroups

I hope you will find the information return by this query useful.

Advertisements

4 thoughts on “List all Objects and Indexes per Filegroup / Partition

  1. Pingback: All Query | MaulinThaker

  2. Script with improvements. Displays allocated tables in partition schema, too.

    SELECT OBJECT_NAME(i.[object_id]) AS [ObjectName]
    ,i.[index_id] AS [IndexID]
    ,i.[name] AS [IndexName]
    ,i.[type_desc] AS [IndexType]
    ,pf.name AS [Partition Schema]
    ,f.[name] AS [FileGroup]
    ,d.[physical_name] AS [DatabaseFileName]
    FROM [sys].[indexes] i
    LEFT JOIN SYS.partition_schemes pf
    ON pf.[data_space_id] = i.[data_space_id]
    LEFT JOIN [sys].[filegroups] f
    ON f.[data_space_id] = i.[data_space_id]
    LEFT JOIN [sys].[database_files] d
    ON f.[data_space_id] = d.[data_space_id]
    LEFT 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]
    ORDER BY [Partition Schema] DESC, F.name
    GO

    Like

  3. Pingback: Find Out Which Indexes are on which Filegroups using PowerShell And How To Find Other Information | SQL DBA with A Beard

  4. Pingback: Find Out Which Indexes are on which Filegroups using PowerShell And How To Find Other Information - SQL Server - SQL Server - Toad World

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s