How to list all objects and indexes [SQL Server]

Today I had to list which objects belongs to which filegroup.
It’s a simple and useful script that you can take from system catalogues: sys.filegroups, sys.indexes, sys.database_files and sys.data_spaces.

SELECT Object_name(i.[object_id]) AS [ObjectName],
       i.[index_id]               AS [IndexID],
       i.[name]                   AS [IndexName],
       i.[type_desc]              AS [IndexType],
       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]),

Take a look this sample


In the next post I’m going to share How Move Index to other FileGroup.

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s