I’m going to follow up my post on partitioning with a query I wrote while implementing the partitions and cleaning up the empty ones. One of the issues I ran in to was clearly seeing each partition’s range and the number of rows within it. These two questions are answered in different places in the catalog views, sys.partition_range_values and sys.partitions. To build this out, I started with a nice little query from Derek Dieter, adding in a couple joins to get me to sys.partitions. Here’s the result!
select t.name as TableName , ps.name as PartitionScheme , pf.name as PartitionFunction , p.partition_number , p.rows , case when pf.boundary_value_on_right=1 then 'RIGHT' else 'LEFT' end [range_type] , prv.value [boundary] from sys.tables t join sys.indexes i on t.object_id = i.object_id join sys.partition_schemes ps on i.data_space_id = ps.data_space_id join sys.partition_functions pf on ps.function_id = pf.function_id join sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id join sys.partition_range_values prv on pf.function_id = prv.function_id and p.partition_number = prv.boundary_id where i.index_id < 2 --So we're only looking at a clustered index or heap, which the table is partitioned on order by p.partition_number