SQL statistics on which tables use partition tables

Select object_name (P. object_id) as objectname,
I. Name as indexname,
P. index_id as indexid,
DS. Name as partitionscheme,
P. partition_number as partitionnumber,
FG. Name as filegroupname,
Prv_left.value as lowerboundaryvalue,
Prv_right.value as upperboundaryvalue,
Case PF. boundary_value_on_right
When 1 then 'right'
Else 'left' end as range,
P. rows as rows
From SYS. partitions as P
Join SYS. indexes as I
On I. object_id = P. object_id
And I. index_id = P. index_id
Join SYS. data_spaces as DS
On Ds. data_space_id = I. data_space_id
Join SYS. partition_schemes as PS
On ps. data_space_id = Ds. data_space_id
Join SYS. partition_functions as pf
On PF. function_id = ps. function_id
Join SYS. destination_data_spaces as dds2
On dds2.partition _ scheme_id = ps. data_space_id
And dds2.destination _ id = P. partition_number
Join SYS. filegroups as FG
On FG. data_space_id = dds2.data _ space_id
Left join SYS. partition_range_values as prv_left
On ps. function_id = prv_left.function_id
And prv_left.boundary_id = P. partition_number-1
Left join SYS. partition_range_values as prv_right
On ps. function_id = prv_right.function_id
And prv_right.boundary_id = P. partition_number
Where
Objectproperty (P. object_id, 'ismsshipped ') = 0
Union all

-- Non-partitioned table/Indexes

Select
Object_name (P. object_id) as objectname,
I. Name as indexname,
P. index_id as indexid,
Null as partitionscheme,
P. partition_number as partitionnumber,
FG. Name as filegroupname,
Null as lowerboundaryvalue,
Null as upperboundaryvalue,
Null as boundary,
P. rows as rows
From SYS. partitions as P
Join SYS. indexes as I
On I. object_id = P. object_id
And I. index_id = P. index_id
Join SYS. data_spaces as DS
On Ds. data_space_id = I. data_space_id
Join SYS. filegroups as FG
On FG. data_space_id = I. data_space_id
Where
Objectproperty (P. object_id, 'ismsshipped ') = 0
Order
Objectname,
Indexid,
Partitionnumber;

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/htl258/archive/2009/04/20/4095110.aspx