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;
There are comments.
