Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SELECT
sc.name + N'.' + so.name as [Schema.Table],
si.index_id as [Index ID],
si.type_desc as [Structure],
si.name as [Index],
stat.row_count AS [Rows],
stat.in_row_reserved_page_count * 8./1024./1024. as [In-Row GB],
stat.lob_reserved_page_count * 8./1024./1024. as [LOB GB],
p.partition_number AS [Partition #],
pf.name as [Partition Function],
CASE pf.boundary_value_on_right
WHEN 1 then 'Right / Lower'
ELSE 'Left / Upper'
END as [Boundary Type],
prv.value as [Boundary Point],
fg.name as [Filegroup]
FROM sys.partition_functions AS pf
JOIN sys.partition_schemes as ps on ps.function_id=pf.function_id
JOIN sys.indexes as si on si.data_space_id=ps.data_space_id
JOIN sys.objects as so on si.object_id = so.object_id
JOIN sys.schemas as sc on so.schema_id = sc.schema_id
JOIN sys.partitions as p on
si.object_id=p.object_id
and si.index_id=p.index_id
LEFT JOIN sys.partition_range_values as prv on prv.function_id=pf.function_id
and p.partition_number=
CASE pf.boundary_value_on_right WHEN 1
THEN prv.boundary_id + 1
ELSE prv.boundary_id
END
/* For left-based functions, partition_number = boundary_id,
for right-based functions we need to add 1 */
JOIN sys.dm_db_partition_stats as stat on stat.object_id=p.object_id
and stat.index_id=p.index_id
and stat.index_id=p.index_id and stat.partition_id=p.partition_id
and stat.partition_number=p.partition_number
JOIN sys.allocation_units as au on au.container_id = p.hobt_id
and au.type_desc ='IN_ROW_DATA'
/* Avoiding double rows for columnstore indexes. */
/* We can pick up LOB page count from partition_stats */
JOIN sys.filegroups as fg on fg.data_space_id = au.data_space_id
ORDER BY [Schema.Table], [Index ID], [Partition Function], [Partition #];
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.