Find Partitioned Tables on Filegroups
Recently I needed to discover which tables are on which filegroups, but with the extra complexity of accounting for the partitions: partitioned tables can exist across multiple filegroups. The script below will show the tables name, schema, partition schema and partition function, and which filegroup it is stored on. Tables that are split across multiple filegroups will appear twice.
;with ranges ( schemaId, dataspace_id, table_name, partition_Function_Name, Partition_Schema_Name)
as
(
SELECT obj.schema_id, i.data_space_id, obj.name as Table_Name, func.name as partition_Function_Name, ps.name as Partition_Schema_Name
FROM sys.indexes i
INNER JOIN sys.partition_schemes ps
ON i.data_space_id = ps.data_space_id
INNER JOIN sys.partition_functions func
on ps.function_id = func.function_id
INNER JOIN sys.all_objects obj
ON i.[object_id] = obj.[object_id]
WHERE i.data_space_id = ps.data_space_id
AND obj.type = 'U'
)
SELECT table_name, s.name, partition_Function_Name, Partition_Schema_Name, fg.name as [FileGroup_Name]
FROM ranges r
LEFT OUTER JOIN sys.partition_schemes ps ON r.dataspace_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, r.dataspace_id)=fg.data_space_id
INNER JOIN sys.schemas s on s.schema_id = r.schemaId
group by table_name, s.name, partition_Function_Name, Partition_Schema_Name, fg.name
References:
http://www.jasonstrate.com/2013/01/determining-file-group-for-a-table/