Subscribe to this blog
Briefcase
Print
The other day, I was trying to find out which partition is on which filegroup for a a partitioned table. I can get it by script out the partition scheme and look at the defintition to figure out. But I want to have a query to do this for me so I can use it in my script. After some reasearch on system tables/views, I finally figured it out.
The following will return the filegroup vs partition number:
use MyDatabase
go
select ds.name as [filegroup], ds.type,
ps.name partition_scheme, p.partition_number, p.rows
from sys.data_spaces ds
inner join sys.destination_data_spaces dds
on ds.data_space_id = dds.data_space_id
inner join sys.partition_schemes ps
on ps.data_space_id = dds.partition_scheme_id
inner join sys.partitions p
on p.partition_number = dds.destination_id
and p.object_id = object_id('dbo.mytable')
order by ds.name, ps.name asc
Posted by ken.trock on 22 January 2010
Nice. You got this 2 years ago. But it does return too many rows. I constrict it with tablename and scheme name. Also added partition function info and parameter counts. it's not perfect but gives a nice overview.
select ds.name as 'filegroup', ds.type, ps.name 'partition_scheme', p.partition_number, p.rows,
f.name as 'function', r.*
from sys.data_spaces ds
inner join sys.destination_data_spaces dds on ds.data_space_id = dds.data_space_id
inner join sys.partition_schemes ps on ps.data_space_id = dds.partition_scheme_id
inner join sys.partitions p on p.partition_number = dds.destination_id
inner join sys.partition_parameters pp on pp.function_id = ps.function_id
inner join sys.partition_functions f on f.function_id = pp.function_id
inner join sys.partition_range_values r on r.function_id = f.function_id
where
p.object_id = object_id(<tablename>) and
ps.name = (<scheme name>)
order by ds.name, ps.name asc