Script to find large indexes in a SQL Server Database

— Ensure a USE statement has been executed first.
SELECT i.[name] AS IndexName
,SUM(s.[used_page_count]) * 8 / 1024 AS IndexSizeMb
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
AND s.[index_id] = i.[index_id]
GROUP BY i.[name] Having SUM(s.[used_page_count]) * 8 / 1024>100
ORDER BY i.[name]