要创建在当前有分区的数据库中:
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
