View SQL table partition information

Code Create procedure [DBO]. [sp_show_partition_range]
(
@ Partition_table nvarchar (255) = NULL
, @ Partition_function nvarchar (255) = NULL
)
As
Begin
Set nocount on

Declare @ function_id int
Set @ function_id = NULL

-- Get @ function_id base on @ partition_table
If Len (@ partition_table)> 0 begin
Select @ function_id = S. function_id
From SYS. Indexes I
Inner join SYS. partition_schemes s
On I. data_space_id = S. data_space_id
Where I. index_id <2
And I. object_id = object_id (@ partition_table)

If @ function_id is null
Return 1
End

-- Get @ function_id base on @ partition_function
If Len (@ partition_function)> 0 begin
Select @ function_id = function_id
From SYS. partition_functions
Where name = @ partition_function

If @ function_id is null
Return 1
End

-- Get partition range
Select partition_function = f. Name
, T. Partition
, T. minval
, Value = case when F. boundary_value_on_right = 1 then' <= Val <'else' <Val <= 'end
, T. maxval
From (
Select H. function_id
, Partition = H. boundary_id
, Minval = L. Value
, Maxval = H. Value
From SYS. partition_range_values H
Left join SYS. partition_range_values L
On H. function_id = L. function_id and H. boundary_id = L. boundary_id + 1

Union all

Select function_id
, Partition = max (boundary_id) + 1
, Minval = max (value)
, Maxval = NULL
From SYS. partition_range_values
Group by function_id
) T
Inner join SYS. partition_functions F
On T. function_id = f. function_id
Where F. function_id = @ function_id
Or @ function_id is null
Order by 1, 2
End

View the partition range:
Sp_show_partition_range 'so _ tb_ps '-- table name

View partition data distribution

 

Code Select Partition Number = $ partition. pf_so (orderdate)
, Number of rows = count (*)
, Minimum value = min (orderdate)
, Maximum value = max (orderdate)
From DBO. So
Group by $ partition. pf_so (orderdate)
Order by 1
-- Pf_so is a table partition function.