Get number of rows per partition

How can you get the number of rows in each partition of your partitioned tables?
Here is a simple query to retrieve that data, including the boundary value for each partition:

SELECT	    
     p.partition_id                             as PartitionID
    ,t.name                                     as [Table]
    ,ps.name                                    as PartitionScheme
    ,pf.name                                    as PartitionFunction
    ,p.partition_number                         as [Partition]
    ,p.rows                                     as [Rows]
    ,prv.value                                  as Boundary
    --,pf.type_desc                               as BoundaryType
    ,case when pf.boundary_value_on_right = 1 
        then 'Right'
        else 'Left'
        end                                     as BoundarySide

FROM	
    sys.tables t
    inner join sys.partitions p 
        on t.object_id = p.object_id 
        and p.index_id = 1
    inner join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
    inner join sys.data_spaces ds on i.data_space_id = ds.data_space_id
    inner join sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
    inner join sys.partition_functions pf on ps.function_id = pf.function_id
    left outer join sys.partition_range_values prv 
        on pf.function_id = prv.function_id
        and p.partition_number = prv.boundary_id
WHERE	
    is_ms_shipped = 0   
    and exists (select 1 from sys.partitions p2
                where t.object_id = p2.object_id
                and partition_number > 1)
order by 
     t.name 
    ,p.partition_number desc

This will show you

  • Tables that have at least two partitions. If you also want to see the number of rows for all the other tables, remove the … and exits (…) from the where-clause.
  • Only one entry per partition. If you want to see an entry per partitioned index, remove and p.index_id = 1 from the join-condition.
  • One Response to Get number of rows per partition

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    %d bloggers like this: