Davide Mauri Blog

To obtain all information on how a table has been partitioned I usually use two views:

The first just list all the partitened objects, showing the used partitioned scheme and function:

CREATE view [sys2].[partitioned_objects]
as
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

That sys2.partitioned_object views is very useful to see how a table has been partitioned:

select * from sys2.partitioned_objects where object_id = object_id('table_name')

with the above query will list table and related indexes partition information.

Once you know what partition scheme and function the table and related indeexes uses for partitioning you may also what to see how many partition actually exists, what range values are used and so on.

This view will help you:

create view [sys2].[partitioned_objects_range_values]
as
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

To use it, just use as the one showed before:

select * from sys2.partitioned_objects_range_values where object_id = object_id('table_name')

The query will produce a list with all partitions and all range values for the object 'table_name'

I've put my "system" views into a schema named sys2, so be sure to create it or change that create view statement to create views in your own schema.