Partition Details and Row Counts
You will likely find the following query useful if you work with partitioned objects. I developed this when I first started using table partitioning in order to verify proper partition boundaries, filegroups and row counts. Not only does this provide much more information that can be obtained by querying the underlying table with the partition function to get partition numbers, it runs much faster because only catalog views are used.
As-is, the query includes both partitioned and non-partitioned user objects in the context database but you can customize the WHERE clauses as desired. I think this query would make a perfect source for a SSMS custom report so that it can be easily invoked from the SSMS Object explorer. That’s on my to-do list.
Query for Partition Details Using Catalog Views
|
--paritioned table and index details 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 BY ObjectName, IndexID, PartitionNumber; |
Here is some sample output.
|
ObjectName |
IndexName |
IndexID |
PartitionScheme |
PartitionNumber |
FileGroupName |
LowerBoundaryValue |
UpperBoundaryValue |
Boundary |
Rows |
|
SalesTransactions_NonPartitioned |
cdx_SalesTransactions_SalesTransactionTime |
1 |
NULL |
1 |
PartitioningDemo_Data |
NULL |
NULL |
NULL |
6576000 |
|
SalesTransactions_NonPartitioned |
idx_SalesTransactions_StoreID_ProductID |
2 |
NULL |
1 |
PartitioningDemo_Index |
NULL |
NULL |
NULL |
6576000 |
|
SalesTransactions_NonPartitioned |
idx_SalesTransactions_CustomerID |
3 |
NULL |
1 |
PartitioningDemo_Index |
NULL |
NULL |
NULL |
6576000 |
|
SalesTransactions_Partitioned |
cdx_SalesTransactions_SalesTransactionTime |
1 |
PS_SalesTransactions_Data |
1 |
PartitioningDemo_Data |
NULL |
2006-01-01 0:00:00 |
RIGHT |
0 |
|
SalesTransactions_Partitioned |
cdx_SalesTransactions_SalesTransactionTime |
1 |
PS_SalesTransactions_Data |
2 |
PartitioningDemo_Data |
2006-01-01 0:00:00 |
2006-02-01 0:00:00 |
RIGHT |
0 |
|
SalesTransactions_Partitioned |
cdx_SalesTransactions_SalesTransactionTime |
1 |
PS_SalesTransactions_Data |
3 |
PartitioningDemo_Data |
2006-02-01 0:00:00 |
2006-03-01 0:00:00 |
RIGHT |
0 |
|
SalesTransactions_Partitioned |
cdx_SalesTransactions_SalesTransactionTime |
1 |
PS_SalesTransactions_Data |
4 |
PartitioningDemo_Data |
2006-03-01 0:00:00 |
2006-04-01 0:00:00 |
RIGHT |
0 |
|
SalesTransactions_Partitioned |
cdx_SalesTransactions_SalesTransactionTime |
1 |
PS_SalesTransactions_Data |
5 |
PartitioningDemo_Data |
2006-04-01 0:00:00 |
2006-05-01 0:00:00 |
RIGHT |
180000 |
|
SalesTransactions_Partitioned |
cdx_SalesTransactions_SalesTransactionTime |
1 |
PS_SalesTransactions_Data |
6 |
PartitioningDemo_Data |
2006-05-01 0:00:00 |
2006-06-01 0:00:00 |
RIGHT |
186000 |
|
SalesTransactions_Partitioned |
cdx_SalesTransactions_SalesTransactionTime |
1 |
PS_SalesTransactions_Data |
7 |
PartitioningDemo_Data |
2006-06-01 0:00:00 |
2006-07-01 0:00:00 |
RIGHT |
180000 |
|
SalesTransactions_Partitioned |
cdx_SalesTransactions_SalesTransactionTime |
1 |
PS_SalesTransactions_Data |
8 |
PartitioningDemo_Data |
2006-07-01 0:00:00 |
2006-08-01 0:00:00 |
RIGHT |
186000 |
|
SalesTransactions_Partitioned |
cdx_SalesTransactions_SalesTransactionTime |
1 |
PS_SalesTransactions_Data |
9 |
PartitioningDemo_Data |
2006-08-01 0:00:00 |
2006-09-01 0:00:00 |
RIGHT |
186000 |
|
SalesTransactions_Partitioned |
cdx_SalesTransactions_SalesTransactionTime |
1 |
PS_SalesTransactions_Data |
10 |
PartitioningDemo_Data |
2006-09-01 0:00:00 |
2006-10-01 0:00:00 |
RIGHT |
180000 |
|
SalesTransactions_Partitioned |
cdx_SalesTransactions_SalesTransactionTime |
1 |
PS_SalesTransactions_Data |
11 |
PartitioningDemo_Data |
2006-10-01 0:00:00 |
2006-11-01 0:00:00 |
RIGHT |
186000 |
|
SalesTransactions_Partitioned |
cdx_SalesTransactions_SalesTransactionTime |
1 |
PS_SalesTransactions_Data |
12 |
PartitioningDemo_Data |
2006-11-01 0:00:00 |
2006-12-01 0:00:00 |
RIGHT |
180000 |
Legacy Comments
|
shan
2012-07-02 |
re: Partition Details and Row Counts Just fabalous, u saved me. |
|
guzmanda
2012-07-02 |
re: Partition Details and Row Counts I created a custom report so that this query can be invoked from the SSMS Object Browser by right-clicking on the desired database or table node. The project is on Codeplex. See my other blog post for more details and the download link: weblogs.sqlteam.com/... |