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/

http://blog.sqlauthority.com/2009/06/01/sql-server-list-all-objects-created-on-all-filegroups-in-database/

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

1 thought on “Find Partitioned Tables on Filegroups”

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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