T-SQL Script to find the size of every index in the database
Posted by Sudarshan Narasimhan on December 15, 2012
I wrote this query to find the size of all indexes in the database for one of my customers. Thought I’d share this script with everyone. Cheers.
CREATE PROCEDURE uspIndexSizeinDB AS -- Author: Sudarshan (TheSQLDude.com) -- Data Created: Dec 15, 2012 -- Description: Calculate the SQL Server index size for all indexes in the current database BEGIN DECLARE @IndexSize BIGINT, @IndexID INT, @IndexName nvarchar(200), @IndexType nvarchar(50), @ObjectID INT SET @IndexSize = 0 create table #tmpresults (ObjName nvarchar(100), IndexID int, IndexName nvarchar(200),[IndexSize(MB)] int, IndexType nvarchar(50)) DECLARE curIndex CURSOR FOR SELECT sysind.object_id, sysind.index_id, sysind.name, sysind.type_desc FROM sys.indexes sysind join sys.sysobjects sysobj on sysind.object_id = sysobj.id where sysind.type>0 and sysobj.type not in ('S','IT') order by sysobj.id asc OPEN curIndex FETCH NEXT FROM curIndex INTO @ObjectID, @IndexID, @IndexName, @IndexType WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @IndexSize = sum(avg_record_size_in_bytes * record_count) FROM sys.dm_db_index_physical_stats(DB_ID(),@ObjectID, @IndexID, NULL, 'detailED') insert into #tmpresults (ObjName, IndexID, IndexName, [IndexSize(MB)], IndexType) SELECT TableName = OBJECT_NAME(@ObjectID), IndexID = @IndexID, IndexName = @IndexName, [IndexSize(MB)] = CONVERT(DECIMAL(16,1),(@IndexSize / (1024.0 * 1024))), IndexType = @IndexType FETCH NEXT FROM curIndex INTO @ObjectID, @IndexID, @IndexName, @IndexType END CLOSE curIndex DEALLOCATE curIndex select UPPER(ObjName) as ObjectName, IndexID, IndexName, IndexType, [IndexSize(MB)] from #tmpresults order by [IndexSize(MB)] desc END GO
The SQL Dude! 
GS Sohal said
Thanks a lot
Sudarshan Narasimhan said
You’re welcome.