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