Why is viewing a partitioned table layout so difficult

For some reason the Microsoft SQL Server developers decided not to provide any tools to see how a partitioned table is built. Since it is quite important information I wrote a little sp_help_partition TSQL stored procedure to give an overview. There is an optional object parameter which allows you to filter on a specific partitioned table, or shows you all the partitions in the system.

The procedure is installed on the master database, but because the name starts with sp_... you can run it in any database.

usage:

   1:  exec sp_help_partition 'Orders'

The result of this statement could be:

Table	Partition Scheme	Partition Function	PartitionNumber	FileGroupName	RangeValue
Orders	monthly_scheme	monthly_bucket	1	History	2006-06-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	2	Partition_02	2006-07-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	3	Partition_03	2006-08-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	4	Partition_04	2006-09-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	5	Partition_05	2006-10-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	6	Partition_06	2006-11-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	7	Partition_07	2006-12-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	8	Partition_08	2007-01-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	9	Partition_09	2007-02-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	10	Partition_10	2007-03-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	11	Partition_11	2007-04-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	12	Partition_12	2007-05-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	13	Partition_01	2999-01-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	14	Partition_99	NULL

You can download the script sp_help_partition.sql or read the code below:

   1:  USE master

   2:  IF EXISTS(SELECT * FROM sys.objects WHERE name = 'sp_help_partition')

   3:    BEGIN

   4:      PRINT 'Dropping procedure sp_help_partition...'

   5:      DROP PROCEDURE sp_help_partition 

   6:    END

   7:  print 'Creating procedure sp_help_partition...'

   8:  GO 

   9:  CREATE PROCEDURE sp_help_partition (@object_name sysname = NULL)

  10:  AS

  11:   

  12:  declare @db_name sysname

  13:  select @db_name = db_name()

  14:   

  15:  if @object_name IS NULL

  16:   begin

  17:      exec ('use ' + @db_name + '

  18:      select 

  19:            object_name(i.[object_id]) as [Table],

  20:            ps.name                    as PartitionScheme,

  21:            pf.name                    as PartitionFunction,

  22:            dds.destination_id         as PartitionNumber,

  23:            fg.Name                    as FileGroupName,

  24:            prv.value                  as RangeValue

  25:      from .sys.indexes i 

  26:      inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id

  27:      inner join sys.partition_functions pf on ps.function_id = pf.function_id

  28:      inner join sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id

  29:      inner join sys.filegroups fg on fg.data_space_id = dds.data_space_id

  30:      left  join sys.partition_range_values prv on prv.boundary_id = dds.destination_id

  31:      where i.type = 1 ')

  32:    end

  33:  else

  34:    begin

  35:      exec ('use ' + @db_name + '

  36:      select 

  37:            object_name(i.[object_id]) as [Table],

  38:            ps.name                    as PartitionScheme,

  39:            pf.name                    as PartitionFunction,

  40:            dds.destination_id         as PartitionNumber,

  41:            fg.Name                    as FileGroupName,

  42:            prv.value                  as RangeValue

  43:      from sys.indexes i 

  44:      inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id

  45:      inner join sys.partition_functions pf on ps.function_id = pf.function_id

  46:      inner join sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id

  47:      inner join sys.filegroups fg on fg.data_space_id = dds.data_space_id

  48:      left  join sys.partition_range_values prv on prv.boundary_id = dds.destination_id

  49:      where i.type = 1 and 

  50:            i.[object_id] = object_id(''' + @object_name + ''')')

  51:    end

  52:  RETURN(0)

  53:  GO

  54:  IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'sp_help_partition' AND type = 'P')

  55:    BEGIN

  56:      PRINT 'PROCEDURE sp_help_partition has been created...'

  57:    END

  58:  ELSE

  59:    BEGIN

  60:      PRINT 'PROCEDURE sp_help_partition has NOT been created due to errors...'

  61:    END

  62:  GO

For more information about table partitioning in Microsoft SQL Server 2005 read the excellent whitepaper by Kimberley Tripp