Wednesday, July 1, 2009

TSQL : Index Size Script

returns types and sizes of indexes...
SELECT   sys_schemas.name AS SchemaName
,sys_objects.name AS TableName
,sys_indexes.name AS IndexName
,sys_indexes.type_desc AS IndexType
,partition_stats.used_page_count * 8 AS IndexSizeKB
,CAST(partition_stats.used_page_count * 8 / 1024.00 AS Decimal(10,3))AS IndexSizeMB
,CAST(partition_stats.used_page_count * 8 / 1048576.00 AS Decimal(10,3)) AS IndexSizeGB
FROM sys.dm_db_partition_stats partition_stats
INNER JOIN sys.indexes sys_indexes
ON partition_stats.[object_id] = sys_indexes.[object_id] 
AND partition_stats.index_id = sys_indexes.index_id
AND sys_indexes.type_desc <> 'HEAP'
INNER JOIN sys.objects sys_objects
ON sys_objects.[object_id] = partition_stats.[object_id] 
INNER JOIN sys.schemas sys_schemas  
ON sys_objects.[schema_id] = sys_schemas.[schema_id] 
AND sys_schemas.name <> 'SYS'
--WHERE partition_stats.[object_id] = object_id('dbo.TableName')
ORDER BY 1,2,3,4