SQL Server - Query partitions with boundaries

Check what partitions are you using in the database. Super-useful script to quickly list all partitioned tables with information about file groups, partition schemas and partition functions.

HINT:  Add where clause to focus on particular table/schema…

Script source: https://sqlity.net/en/2483/partition-boundaries/

SELECT 
	f.NAME AS file_group_name
	,SCHEMA_NAME(t.schema_id) AS table_schema
	,t.name AS table_name
	,p.partition_number
	,ISNULL(CAST(left_prv.value AS VARCHAR(MAX)) + CASE 
			WHEN pf.boundary_value_on_right = 0
				THEN ' < '
			ELSE ' <= '
			END, '-INF < ') + 'X' + ISNULL(CASE 
			WHEN pf.boundary_value_on_right = 0
				THEN ' <= '
			ELSE ' < '
			END + CAST(right_prv.value AS NVARCHAR(MAX)), ' < INF') AS range_desc
	,ps.name AS partition_schem_name
	,pf.name AS partition_function_name
	,left_prv.value AS left_boundary
	,right_prv.value AS right_boundary
FROM sys.partitions p
	JOIN sys.tables t ON p.object_id = t.object_id
	JOIN sys.indexes i ON p.object_id = i.object_id
		AND p.index_id = i.index_id
	JOIN sys.allocation_units au ON p.hobt_id = au.container_id
	JOIN sys.filegroups f ON au.data_space_id = f.data_space_id
	LEFT JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
	LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
	LEFT JOIN sys.partition_range_values left_prv ON left_prv.function_id = ps.function_id
		AND left_prv.boundary_id + 1 = p.partition_number
	LEFT JOIN sys.partition_range_values right_prv ON right_prv.function_id = ps.function_id
		AND right_prv.boundary_id = p.partition_number

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
file_group_namesysnameName of data space, unique within the database.
table_schemasysnameName of the schema
table_namesysnameName of the table
partition_numberintIs a 1-based partition number within the owning index or heap. For non-partitioned tables and indexes, the value of this column is 1.
range_descnvarchar(max)Human readable description of partition range using comparison operators.
partition_schema_namesysnameName of the partition schema used.
partition_function_namesysnameName of the partition function used.
left_boundarysql_variantThe actual boundary value.
right_boundarysql_variantThe actual boundary value.

Leave a Reply