Wednesday, July 4, 2007

A small update to the sp_help_partition procedure.

One of the flaws in the sp_help_partition function I posted earlier is the lack of information if the partition function is a Range RIGHT or a Range LEFT function. To make that clearer I added a check on the boundary_value_on_right field from the system tables. Also added an order by to make the presentation nicer.

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
    WHERE i.type = 1 
I hope this version works better for you. Download the complete script.

No comments: