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)
SELECT obj.schema_id, i.data_space_id, as Table_Name, as partition_Function_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,, partition_Function_Name, Partition_Schema_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,, partition_Function_Name, Partition_Schema_Name,


Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

One thought on “Find Partitioned Tables on Filegroups”

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 )

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