Finding Objects by Filegroup

Have you ever found yourself with the need to locate objects in a particular filegroup? When I first found myself with this need I thought to myself “No problem, I will just join sys.indexes with sys.filegroups.” Only sys.filegroups does not have anything upon which to join, instead we have a data_space_id.

To join sys.indexes with sys.filegroups we need to follow Alice down the rabbit hole. First we need to join sys.indexes with sys.partitions on object_id and index_id to obtain the partition_number. Next we need to left outer join sys.indexes with sys.partition_schemes on data_space_id. Our next join is a left outer join with sys.destination_data_spaces on sys.partition_schemes.data_space_id = sys.destination_data_spaces.partition_scheme_id and sys.partitons.partiton_number = sys.destination_data_spaces.destination_id. Finally we join with either sys.destination_data_spaces (if the table is partitioned) or sys.indexes (if the table is not partitioned) on data_space_id.

Below is a query that takes it one step further and joins sys.filegroups with sys.database_files. I find this query useful when performing maintenance on files within a particular filegroup or file system volume.

SELECT
    OBJECT_SCHEMA_NAME(t.object_id) AS schema_name,
    t.name AS table_name,
    i.index_id,
    i.object_id,
    i.name AS index_name,
    . Next we need to joinp.partition_number,
    fg.name AS filegroup_name,
    df.name AS datafile_name,
    df.physical_name,
    p.rows AS row_count,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM
    sys.tables t
    INNER JOIN sys.indexes i
    ON t.object_id = i.object_id
    INNER JOIN sys.partitions p
    ON i.object_id = p.object_id
       AND i.index_id = p.index_id
    LEFT OUTER JOIN sys.partition_schemes ps
    ON i.data_space_id = ps.data_space_id
    LEFT OUTER JOIN sys.destination_data_spaces dds
    ON ps.data_space_id = dds.partition_scheme_id
       AND p.partition_number = dds.destination_id
    INNER JOIN sys.filegroups fg
    ON COALESCE(dds.data_space_id, i.data_space_id) = fg.data_space_id
    INNER JOIN sys.database_files AS df
    ON fg.data_space_id = df.data_space_id
    CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), i.object_id,
                           i.index_id, p.partition_number,
                           DEFAULT) AS ips;

Comments

Brian Bentley

Brian Bentley

I am a database administrator who lives in Cary, North Carolina. I like traditional American and Irish music, jazz, hiking and sharing everything I have learned so far.

I like to blog about music and technology. I am currently a Microsoft Certified Solutions Expert Data Platform as well as a Microsoft Certified Solutions Associate in SQL Server

.