Table Partitioning Query – Tables, Boundaries and Row Counts

The query below provides useful information on any partitioned tables in a database, and the partition boundaries and row counts.


-- Partitioned tables, boundaries and row counts
WITH PartitionRows AS
(
  SELECT
    schema_name(o.schema_id) AS SchemaName,
    o.name AS TableName,
    p.partition_number,
    sum(p.rows) AS Row_count
  FROM sys.partitions AS p
  JOIN sys.tables AS NTables 
    ON p.object_id = NTables.object_id
  JOIN sys.objects AS o ON p.object_id = o.object_id
  GROUP BY o.schema_id, o.name, partition_number, p.data_compression_desc
)
 
, PartitionBoundaries AS
(
  SELECT 
    schema_name(st.Schema_id) SchemaName ,
    st.name TableName ,
    c.name PartitionColumn ,
    sp.partition_number, 
    CAST(prv.value AS nvarchar(50)) boundary_value
  FROM sys.tables AS st 
  JOIN sys.indexes AS si  
    ON st.object_id = si.object_id 
    AND si.index_id <2 
  JOIN sys.partitions AS sp 
    ON sp.object_id = st.object_id 
    AND sp.index_id = si.index_id 
  JOIN sys.partition_schemes AS ps 
    ON ps.data_space_id = si.data_space_id 
  JOIN sys.partition_range_values AS prv 
    ON prv.function_id = ps.function_id 
    AND prv.boundary_id = sp.partition_number 
  JOIN sys.index_columns ic 
    ON(ic.partition_ordinal > 0 
    AND ic.index_id = si.index_id 
    AND ic.object_id = st.object_id) 
  JOIN sys.columns c 
    ON(c.object_id = ic.object_id 
    AND c.column_id = ic.column_id)
)
 
, CTE AS
(
  SELECT pr.SchemaName, pr.TableName, pr.partition_number, pb.PartitionColumn, pr.Row_Count, pb.boundary_value
  FROM PartitionRows pr
  JOIN PartitionBoundaries pb
    ON pr.SchemaName = pb.SchemaName
    AND pr.TableName = pb.TableName
    AND pr.partition_number = pb.partition_number
)
 
SELECT *
FROM CTE 
WHERE partition_number > 1
ORDER BY SchemaName, TableName, partition_number




Categories: Partitioning

%d bloggers like this: