05/11/2016
Finding Size of Index
Below Store Procedure Provides the Indexes and its sizes for the given table
CREATE PROCEDURE getIndexSizes ( @tblName VARCHAR(500) ) AS EXECUTE AS CALLER; BEGIN SELECT OBJECT_NAME(i.object_id) AS TableName , i.name AS IndexName , i.index_id AS IndexID , ( 8 * SUM(a.used_pages) ) / 1024 AS 'Indexsize(MB)' FROM sys.indexes AS i JOIN sys.partitions AS p ON p.object_id = i.object_id AND p.index_id = i.index_id JOIN sys.allocation_units AS a ON a.container_id = p.partition_id WHERE OBJECT_NAME(i.object_id) = @tblName GROUP BY i.object_id , i.index_id , i.name ORDER BY OBJECT_NAME(i.object_id) , i.index_id; END;