Simple Table and Index Breakdown

This script provides a breakdown with row count and partition information of tables and indexes. It is in essence merely a cut down version of the final Detailed Table and Index Breakdown script.

--http://jongurgul.com/blog/simple-table-index-breakdown/
SELECT
 SCHEMA_NAME(ao.[schema_id]) [SchemaName]
,ao.[name] [ObjectName]
,i.[name] [IndexName]
,i.[type_desc] [IndexType]
,p.[partition_number] [PartitionNumber]
--,p.[data_compression_desc] [Compression]
,ds.[name] [PartitionName]
,p.[rows] [NumberOfRows]
,prv.[value] [LowerBoundaryValue]
,prv2.[value] [UpperBoundaryValue]
FROM sys.partition_functions pf
INNER JOIN sys.partition_schemes ps ON pf.[function_id] = ps.[function_id]
RIGHT OUTER JOIN sys.partitions p
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.all_objects ao ON i.[object_id] = ao.[object_id] ON ps.[data_space_id] = ds.[data_space_id]
LEFT OUTER JOIN sys.partition_range_values prv ON ps.[function_id] = prv.[function_id] AND p.[partition_number] - 1 = prv.[boundary_id]
LEFT OUTER JOIN sys.partition_range_values prv2 ON ps.[function_id] = prv2.[function_id] AND prv2.[boundary_id] = p.[partition_number]
WHERE ao.[is_ms_shipped] = 0 
--AND SCHEMA_NAME(ao.[schema_id]) ='dbo' 
--AND ao.[name] LIKE '%%' 
ORDER BY SCHEMA_NAME(ao.[schema_id]),ao.[name]

http://gallery.technet.microsoft.com/scriptcenter/Simple-Table-and-Index-61f93894

Leave a Reply