Partitioning: Three useful query

http://weblogs.sqlteam.com/dmauri/archive/2006/04/20/9646.aspx

DECLARE @PartTables Table (DBName sysname, TableName sysname, PartitionScheme sysname)


INSERT INTO @PartTables
EXEC sp_msforeachdb '    select DISTINCT ''[?]'' AS DBName, t.name As TableName, s.name AS PartitionScheme
                        from [?].sys.indexes i
                        join [?].sys.partition_schemes s
                            on i.data_space_id = s.data_space_id
                        join [?].sys.tables t
                            on i.object_id = t.object_id'

SELECT * FROM @PartTables


DECLARE @OBJECTNAME sysname = 'XXXXX'

select distinct
   p.object_id,
   index_name = i.name,
   index_type_desc = i.type_desc,
   partition_scheme = ps.name,
   data_space_id = ps.data_space_id,
   function_name = pf.name,
   function_id = ps.function_id
from
   sys.partitions p
inner join
   sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
inner join
   sys.data_spaces ds on i.data_space_id = ds.data_space_id
inner join
   sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
inner join
   sys.partition_functions pf on ps.function_id = pf.function_id
where p.object_id = object_id(@OBJECTNAME)




DECLARE @OBJECTNAME sysname = 'XXXX'


select
   p.object_id,
   p.index_id,
   p.partition_number,
   p.rows,
   index_name = i.name,
   index_type_desc = i.type_desc,
   i.data_space_id,
   pf.function_id,
   pf.type_desc,
   pf.boundary_value_on_right,
   destination_data_space_id = dds.destination_id,
   prv.parameter_id,
   prv.value
from
   sys.partitions p
inner join
   sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
inner join
   sys.data_spaces ds on i.data_space_id = ds.data_space_id
inner join
   sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
inner join
   sys.partition_functions pf on ps.function_id = pf.function_id
inner join
   sys.destination_data_spaces dds on dds.partition_scheme_id = ds.data_space_id and p.partition_number = dds.destination_id
left outer join
   sys.partition_range_values prv on prv.function_id = ps.function_id and p.partition_number = prv.boundary_id
where p.object_id = object_id(@OBJECTNAME)