find partitions, row count of each partition of a partition table

21 Feb 2020 20971 views 0 minutes to read Contributors

		
			
T-SQL
			
Transact-SQL
			

			
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
		)
			

				

					

				

					
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
				
						
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
		)