Get partition bounds and number of rows

Sometimes, you need to have a synthetic view of the partition usage of a table.
For a specific table, the following query returns for each non-empty partition:

• partition number
• associated filegroup
• number of rows of the partition
• partition bounds (including RIGHT or LEFT inclusion)
• number of rows in the partitions versus number of rows of the whole table (as a percentage)

SELECT
 '[' + s.name + '].[' + t.name + ']' AS TableName,
 p.partition_number AS PartitionNumber,
 CAST(prv_left.value AS VARCHAR)
 + CASE pf.boundary_value_on_right WHEN 1 THEN ' <= X < ' ELSE ' < X <= ' END
 + CAST(prv_right.value AS VARCHAR) AS PartitionBounds,
 p.[rows] AS PartitionNumberOfRows,
 fg.name AS PartitionFilegroup,
 CAST((100 * p.[rows]) / ii.[rows] AS VARCHAR) + '%' AS SizeRatio
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN sys.indexes i ON i.object_id = t.object_id
INNER JOIN sysindexes ii ON ii.id = t.object_id
INNER JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id = i.index_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id
INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
LEFT JOIN sys.partition_range_values prv_left
 ON prv_left.function_id = pf.function_id
 AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values prv_right
 ON prv_right.function_id = pf.function_id
 AND prv_right.boundary_id = p.partition_number
WHERE i.[type] IN (0, 1) /* Index of type HEAP or CLUSTERED */
AND s.name = 'your_schema_name'
AND t.name = 'your_table_name'
AND p.[rows] > 0
AND ii.indid IN (0, 1)
ORDER BY s.name, t.name, prv_right.value

This may be useful to detect space-consuming partitions, to ensure partition distribution is homogenous, to ensure filegroups are correctly mapped over the partition bounds, etc.