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
