Show Partitioning Information

sp_help_partition.sql

USE master
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'sp_help_partition')
  BEGIN
    PRINT 'Dropping procedure sp_help_partition...'
    DROP PROCEDURE sp_help_partition
  END
print 'Creating procedure sp_help_partition...'
GO
CREATE PROCEDURE sp_help_partition (@object_name sysname = NULL)
AS

DECLARE @db_name sysname
SELECT @db_name = db_name()

IF @object_name IS NULL
 BEGIN
    EXEC ('use ' + @db_name + '
    SELECT
          object_name(i.[object_id]) as [Table],
          ps.name                    as PartitionScheme,
          pf.name                    as PartitionFunction,
          dds.destination_id         as PartitionNumber,
          fg.Name                    as FileGroupName,
          prv.value                  as RangeValue
    FROM sys.indexes i
      INNER JOIN sys.partition_schemes ps on ps.data_space_id = i.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 = ps.data_space_id
      INNER JOIN sys.filegroups fg on fg.data_space_id = dds.data_space_id
      LEFT  JOIN sys.partition_range_values prv on prv.boundary_id = dds.destination_id
    WHERE i.type = 2 ')
  END
ELSE
  BEGIN
    EXEC ('use ' + @db_name + '
    SELECT
          object_name(i.[object_id]) as [Table],
          ps.name                    as PartitionScheme,
          pf.name                    as PartitionFunction,
          dds.destination_id         as PartitionNumber,
          fg.Name                    as FileGroupName,
          prv.value                  as RangeValue
    FROM sys.indexes i
    INNER JOIN sys.partition_schemes ps on ps.data_space_id = i.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 = ps.data_space_id
    INNER JOIN sys.filegroups fg on fg.data_space_id = dds.data_space_id
    LEFT  JOIN sys.partition_range_values prv on prv.boundary_id = dds.destination_id
    WHERE i.type = 2 and
          i.[object_id] = object_id(''' + @object_name + ''')')
  END
RETURN(0)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'sp_help_partition' AND type = 'P')
  BEGIN
    PRINT 'PROCEDURE sp_help_partition has been created...'
  END
ELSE
  BEGIN
    PRINT 'PROCEDURE sp_help_partition has NOT been created due to errors...'
  END
GO