Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Finding the relationship of a filegroup and a partition

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

Comments

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

Leave a Comment

Please register or log in to leave a comment.