If you are using partitioned tables, this may come in handy. I’ll keep it short and just post the code. Let me know if there are errors or you have improvements.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_help_partition]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_help_partition]
GO

CREATE PROCEDURE dbo.sp_help_partition @OBJECT_NAME sysname = NULL
AS
-------------------------------------------------------------------------------
--
-- Proc: sp_help_partition
-- Author: Norman Kelm, Gerasus Software, (C) 2010
--
-- Like sp_help, but for partitioned table partition info.
--
-- Usage:
-- Return a list of table that are partitioned
-- EXECUTE sp_help_partition
--
-- Return the partitioning information for a table
-- EXECUTE sp_help_partition 'Production.TransactionHistory'
--
-- Revision History:
-- 08/06/2010 - 1.0 - Original version
--
-------------------------------------------------------------------------------
DECLARE @schema sysname
      , @TABLE_NAME sysname

SELECT @schema = CASE WHEN PARSENAME(@OBJECT_NAME,2) IS NULL THEN 'dbo' ELSE PARSENAME(@OBJECT_NAME,2) END
     , @TABLE_NAME = PARSENAME(@OBJECT_NAME,1)

IF @OBJECT_NAME IS NULL
BEGIN
  -- List all paritioned tables when no paramters
  SELECT DISTINCT SCHEMA_NAME(OBJECTPROPERTY(object_id, 'SchemaId')) + '.' + OBJECT_NAME(si.object_id) AS [Name]
    FROM sys.partition_schemes AS ps
   INNER JOIN
         sys.indexes AS si
      ON ps.data_space_id = si.data_space_id
END
ELSE
BEGIN
  IF EXISTS(SELECT 1
              FROM sys.partition_schemes AS ps
             INNER JOIN
                   sys.indexes AS si
                ON ps.data_space_id = si.data_space_id
               AND OBJECT_ID(@OBJECT_NAME) = si.[object_id])
  BEGIN

    -- Borrowed from sp_help
    SELECT [Name]  = o.name
         , [Owner] = USER_NAME(OBJECTPROPERTY(object_id, 'ownerid'))
         , [Schema] = SCHEMA_NAME(OBJECTPROPERTY(object_id, 'SchemaId'))
         , [Type]  = substring(v.name,5,31)
         , [Created_datetime] = o.create_date
     FROM sys.all_objects o
        , master.dbo.spt_values v
    WHERE o.object_id = OBJECT_ID(@OBJECT_NAME)
      AND o.type = substring(v.name,1,2) collate database_default
      AND v.type = 'O9T'  

   SELECT COUNT(*) AS NumberOfPartitions
     FROM sys.partitions  p
    INNER JOIN
          sys.indexes i
       ON p.object_id = i.object_id
      AND p.index_id = i.index_id
    WHERE OBJECT_ID(@OBJECT_NAME) = p.[object_id]
      AND i.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count

    SELECT [Scheme] = ps.name
      FROM sys.partition_schemes AS ps
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count

    SELECT [Function] = pf.name
         , [Type]     = pf.type_desc
         , [fanout]   = pf.fanout
         , [boundary_value_on_right] = pf.boundary_value_on_right
         , [create_date]             = pf.create_date
         , [modify_date]             = pf.modify_date
      FROM sys.partition_functions AS pf
     INNER JOIN
           sys.partition_schemes AS ps
        ON ps.function_id = pf.function_id
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count

    SELECT [Function Parameters] = pf.name
         , [parameter_id] = pp.parameter_id
         , [Type] = st.name
         , pp.max_length
         , pp.precision
         , pp.scale
         , pp.collation_name
      FROM sys.partition_parameters AS pp
     INNER JOIN
           sys.partition_functions AS pf
        ON pf.function_id = pp.function_id
     INNER JOIN
           sys.partition_schemes AS ps
        ON ps.function_id = pf.function_id
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
     INNER JOIN
           sys.types AS st
        ON pp.system_type_id = st.system_type_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count

    SELECT [Function Range Values] = pf.name
         , prv.boundary_id
         , prv.value
      FROM sys.partition_range_values AS prv
     INNER JOIN
           sys.partition_parameters AS pp
        ON prv.function_id = pp.function_id
       ANd prv.parameter_id = pp.parameter_id
     INNER JOIN
           sys.partition_functions AS pf
        ON pf.function_id = pp.function_id
     INNER JOIN
           sys.partition_schemes AS ps
        ON ps.function_id = pf.function_id
     INNER JOIN
           sys.indexes AS si
        ON ps.data_space_id = si.data_space_id
     INNER JOIN
           sys.types AS st
        ON pp.system_type_id = st.system_type_id
       AND OBJECT_ID(@OBJECT_NAME) = si.[object_id]
       AND si.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count
     ORDER BY
           prv.boundary_id

     SELECT tc.CONSTRAINT_NAME
          , cc.CHECK_CLAUSE
       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
       JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS cc
         ON tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
        AND tc.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA
      WHERE tc.TABLE_SCHEMA = @schema
        AND tc.TABLE_NAME   = @TABLE_NAME
        AND tc.CONSTRAINT_TYPE = 'CHECK'
   END
   ELSE
   BEGIN
     DECLARE @error NVARCHAR(255)
     SELECT @error = @OBJECT_NAME + ' is not partitioned!!!'
     RAISERROR (@error, 16, 1)
   END
END
GO