SQL Server 查看分区表（partition table）的分区范围（partition range）
https://www.cnblogs.com/chuncn/archive/2009/02/20/1395165.html
SQL Server 2005 的分区表（partition table）是复杂的，特别是对于初学者来说。不管怎样，我们还是掌握了分区函数（partition function），分区方案（partition scheme），最后终于创建了一个分区表出来。但之后呢，或许你想查看分区表的各个分区分区列的取值范围（这个分区的理论最小值和最大值），虽然可以通过分区函数中定义的边界值（boundary value）来推算出来分区表每个分区（partition）的取值范围，但是事情并不是很简单，你需要一系列繁琐的步骤来实现：首先要找出这个分区表的分区函数；然后确定该分区函数定义的边界值属于 left 或者 right； 最后在脑子里使劲思索几下终于确定了分区列取值范围（partition range）。幸运的是，有了下面的这个存储过程，你的大脑就可以获得解放了。
if exists (select 1 from sys.procedures where name = 'sp_show_partition_range')
drop procedure dbo.sp_show_partition_range
go
--------------------------------------------------------------------------------
-- author : p.c.w.l
-- source : www.sqlstudy.com
-- create : 2008-01-01
-- descr : view partition range by 'partition table' or 'partition function'
--------------------------------------------------------------------------------
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
go
SQL Server 2005 的分区表（partition table）是复杂的，特别是对于初学者来说。不管怎样，我们还是掌握了分区函数（partition function），分区方案（partition scheme），最后终于创建了一个分区表出来。但之后呢，或许你想查看分区表的各个分区分区列的取值范围（这个分区的理论最小值和最大值），虽然可以通过分区函数中定义的边界值（boundary value）来推算出来分区表每个分区（partition）的取值范围，但是事情并不是很简单，你需要一系列繁琐的步骤来实现：首先要找出这个分区表的分区函数；然后确定该分区函数定义的边界值属于 left 或者 right； 最后在脑子里使劲思索几下终于确定了分区列取值范围（partition range）。幸运的是，有了下面的这个存储过程，你的大脑就可以获得解放了。
if exists (select 1 from sys.procedures where name = 'sp_show_partition_range')
drop procedure dbo.sp_show_partition_range
go
--------------------------------------------------------------------------------
-- author : p.c.w.l
-- source : www.sqlstudy.com
-- create : 2008-01-01
-- descr : view partition range by 'partition table' or 'partition function'
--------------------------------------------------------------------------------
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
go