Script to find the size of all Indexes in a database

Continuing with my interest with Indexes, i wrote this script that finds the size of all indexes in a database along with the table and the filegroup on which the index resides. I have used the AdventureWorks2012 database as an example. Please replace the DB name for which you want to find the information.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

USE AdventureWorks2012 go IF OBJECT_ID('tempdb..#Indexdata', 'U') IS NOT NULL DROP TABLE #Indexdata   DECLARE @SizeofIndex BIGINT, @IndexID INT, @NameOfIndex nvarchar(200),@TypeOfIndex nvarchar(50), @ObjectID INT,@IsPrimaryKey INT, @FGroup VARCHAR(20)   create table #Indexdata (name nvarchar(50), IndexID int, IndexName nvarchar(200), SizeOfIndex int, IndexType nvarchar(50), IsPrimaryKey INT,FGroup VARCHAR(20)) DECLARE Indexloop CURSOR FOR SELECT idx.object_id, idx.index_id, idx.name, idx.type_desc ,idx.is_primary_key,fg.name FROM sys.indexes idx join sys.objects so on idx.object_id = so.object_id JOIN sys.filegroups fg ON idx.data_space_id = fg.data_space_id where idx.type_desc != 'Heap' and so.type_desc not in ('INTERNAL_TABLE','SYSTEM_TABLE')   OPEN Indexloop   FETCH NEXT FROM Indexloop INTO @ObjectID, @IndexID, @NameOfIndex, @TypeOfIndex,@IsPrimaryKey,@FGroup   WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @SizeofIndex = sum(avg_record_size_in_bytes * record_count) FROM sys.dm_db_index_physical_stats(DB_ID(),@ObjectID, @IndexID, NULL, 'detailed')   insert into #Indexdata(name, IndexID, IndexName, SizeOfIndex, IndexType,IsPrimaryKey,FGroup) SELECT TableName = OBJECT_NAME(@ObjectID), IndexID = @IndexID, IndexName = @NameOfIndex, SizeOfIndex = CONVERT(DECIMAL(16,1),(@SizeofIndex/(1024.0 * 1024))), IndexType = @TypeOfIndex, IsPrimaryKey = @IsPrimaryKey, FGroup = @FGroup   FETCH NEXT FROM Indexloop INTO @ObjectID, @IndexID, @NameOfIndex,  @TypeOfIndex,@IsPrimaryKey,@FGroup END CLOSE Indexloop DEALLOCATE Indexloop   select name as TableName, IndexName, IndexType, SizeOfIndex AS [Size of index(MB)], case when IsPrimaryKey = 1 then 'Yes' else 'No' End as [IsPrimaryKey] ,FGroup AS [File Group] from #Indexdata order by SizeOfIndex DESC

Snapshot of the output below: