/*
    Author: Filip De Vos
    Change History:
        June 1 2007 - Added indicator for Range Right and Range Left.
*/

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,
          case when boundary_value_on_right = 1 then ''RIGHT'' else ''LEFT'' end [Range], 
          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 and pf.function_id = prv.function_id
    WHERE i.type = 1 
    ORDER by object_name(i.[object_id]), dds.destination_id')
  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,
          case when boundary_value_on_right = 1 then ''RIGHT'' else ''LEFT'' end [Range], 
          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 and pf.function_id = prv.function_id
    WHERE i.type = 1 and
          i.[object_id] = object_id(''' + @object_name + ''')
    ORDER object_name(i.[object_id]), by dds.destination_id')
  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