I’ve been involved with some customers where partitioning is quite complex (40+ databases with multiple partitioning schemes across multiples tables in each database).
One key issue is automation and auditing of the partitioning which if left to a human gets in a right mess. To help this I have a set of stored procedure that we can drop into the master database and return partition meta data for all databases\tables on the instance with a single stored proc call.
The stored proc is handy for getting familiar with the various meta data DMVs used by partitioning:
- sys.partitions
- sys.indexes
- sys.data_spaces
- sys.partition_schemes
- sys.destination_data_spaces
- sys.partition_range_values
An example is below
Your welcome to play with the stored proc code below
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_partition_show] Script Date: 06/12/2011 17:08:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Bob Duffy
-- Create date: 02/02/2011
-- Description: Return Meta data on partitioning for databases and/or tables
-- =============================================
ALTER PROCEDURE [dbo].[sp_partition_show]
@BoundaryValue sql_variant =null
,@TableName sysname =null
,@DatabaseName sysname ='ALL' --Default to current. ALL for all databases
AS
BEGIN
SET NOCOUNT ON;
if @DatabaseName is null set @DatabaseName =DB_NAME()
if @DatabaseName ='ALL' set @DatabaseName =null
declare @spool table (db_name sysname, table_name sysname, partition_number int
, rows int, boundary_value_on_right int, range_value sql_variant, partition_function sysname)
insert into @spool
exec sp_msforeachdb 'use [?]; select db_name() as database_name
,object_name(p.object_id) as table_name,
p.partition_number,
p.rows,
pf.boundary_value_on_right,
prv.value as range_value,
pf.name as partition_function
from
sys.partitions p
inner join
sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
inner join
sys.data_spaces ds on i.data_space_id = ds.data_space_id
inner join
sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
inner join
sys.partition_functions pf on ps.function_id = pf.function_id
inner join
sys.destination_data_spaces dds on dds.partition_scheme_id = ds.data_space_id and p.partition_number = dds.destination_id
left outer join
sys.partition_range_values prv on prv.function_id = ps.function_id and p.partition_number = prv.boundary_id
--where i.index_id =1
order by object_name(p.object_id), p.partition_number'
select * From @spool
where (db_name=@DatabaseName or @DatabaseName is null)
and (table_name =@TableName or @TableName is null)
and partition_function not like '%client%'
and (range_value =CONVERT(datetime,@BoundaryValue) or @BoundaryValue is null)
END
