Answered by:
Find partitions, row count of each partition of a partition table

Question
-
Hi,
We have a partition table in a database. The partition table name is 'STORE'. Can anyone provide me a query by which I can find following information
1) All the partition name of table 'STORE'
2) Row count for each partitionThursday, January 12, 2017 7:06 AM
Answers
-
Hello,
See Detailed list of all tables and their size, it queries the required Information from DMV Partition statistics.
Olaf Helper
[ Blog] [ Xing] [ MVP]- Proposed as answer by Albert_ Zhang Friday, January 13, 2017 1:55 AM
- Marked as answer by arifulhaq Friday, January 13, 2017 5:03 PM
Thursday, January 12, 2017 7:13 AM
All replies
-
Hello,
See Detailed list of all tables and their size, it queries the required Information from DMV Partition statistics.
Olaf Helper
[ Blog] [ Xing] [ MVP]- Proposed as answer by Albert_ Zhang Friday, January 13, 2017 1:55 AM
- Marked as answer by arifulhaq Friday, January 13, 2017 5:03 PM
Thursday, January 12, 2017 7:13 AM -
SELECT
DB_NAME() AS 'DatabaseName'
,OBJECT_NAME(p.OBJECT_ID) AS 'TableName'
,p.index_id AS 'IndexId'
,CASE
WHEN p.index_id = 0 THEN 'HEAP'
ELSE i.name
END AS 'IndexName'
,p.partition_number AS 'PartitionNumber'
,prv_left.value AS 'LowerBoundary'
,prv_right.value AS 'UpperBoundary'
,ps.name as PartitionScheme
,pf.name as PartitionFunction
,CASE
WHEN fg.name IS NULL THEN ds.name
ELSE fg.name
END AS 'FileGroupName'
,CAST(p.used_page_count * 0.0078125 AS NUMERIC(18,2)) AS 'UsedPages_MB'
,CAST(p.in_row_data_page_count * 0.0078125 AS NUMERIC(18,2)) AS 'DataPages_MB'
,CAST(p.reserved_page_count * 0.0078125 AS NUMERIC(18,2)) AS 'ReservedPages_MB'
,CASE
WHEN p.index_id IN (0,1) THEN p.row_count
ELSE 0
END AS 'RowCount'
,CASE
WHEN p.index_id IN (0,1) THEN 'data'
ELSE 'index'
END 'Type'
FROM sys.dm_db_partition_stats p
INNER JOIN sys.indexes i
ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.data_spaces ds
ON ds.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_functions pf
ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.filegroups fg
ON fg.data_space_id = dds.data_space_id
LEFT OUTER JOIN sys.partition_range_values prv_right
ON prv_right.function_id = ps.function_id
AND prv_right.boundary_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values prv_left
ON prv_left.function_id = ps.function_id
AND prv_left.boundary_id = p.partition_number - 1
WHERE
OBJECTPROPERTY(p.OBJECT_ID, 'ISMSSHipped') = 0
AND p.index_id IN (0,1)
GOBest Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
- Proposed as answer by Albert_ Zhang Friday, January 13, 2017 1:55 AM
Thursday, January 12, 2017 7:29 AMAnswerer