![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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
(
@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
查看分区区间:
sp_show_partition_range 'SO_Tb_Ps' --表名
sp_show_partition_range 'SO_Tb_Ps' --表名
查看表分区数据分布情况
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
select
分区编号
=
$partition.PF_SO(OrderDate)
,行数 = count ( * )
,最小值 = min (OrderDate)
,最大值 = max (OrderDate)
from dbo.SO
group by $partition.PF_SO(OrderDate)
order by 1
-- PF_SO为表分区函数
,行数 = count ( * )
,最小值 = min (OrderDate)
,最大值 = max (OrderDate)
from dbo.SO
group by $partition.PF_SO(OrderDate)
order by 1
-- PF_SO为表分区函数