Thursday, March 20, 2014

Get the Partitioned table information in SQL...

In the previous post I have mentioned how to partition SQL Server database tables. But you cannot simply get the information related to those partitioned tables and for that several tables to be considered.
Below query list down the important details about the partitioned table.
   1: SELECT T.Name AS TableName,

   2:        Ps.Name AS PartitionScheme,

   3:        Pf.Name AS PartitionFunction,

   4:        CASE

   5:             WHEN Pf.Boundary_value_on_right = 1 THEN 'RIGHT'

   6:             ELSE 'LEFT'

   7:        END AS RangeType,

   8:        P.Partition_number AS PartitionNumber,

   9:        P.Rows AS NumberOfRows,

  10:        rv.[value] AS RangeValue

  11: FROM   SYS.Tables T

  12:        JOIN SYS.Indexes I

  13:             ON  T.Object_id = I.Object_id

  14:        JOIN SYS.Partition_schemes Ps

  15:             ON  I.Data_space_id = Ps.Data_space_id

  16:        JOIN SYS.Partition_functions Pf

  17:             ON  Ps.Function_id = Pf.Function_id

  18:        JOIN SYS.Partitions P

  19:             ON  I.Object_id = P.Object_id

  20:             AND I.Index_id = P.Index_id

  21:        INNER JOIN sys.partition_range_values rv

  22:             ON  pf.function_id = rv.function_id

  23:             AND p.partition_number = rv.boundary_id

  24: WHERE  I.Index_id < 2 --Filter for Clustered Index

  25:        AND T.[name] = 'PartitionedTable'

  26: ORDER BY

  27:        P.Partition_number
You can add any other additional columns from those joined table if needed.

No comments:

Post a Comment