Script to find the Index size in SQL Server

You can use either of the below queries to find the size of the indexes in your database. Please note that sysindexes is deprecated, so the first script is recommended for the production purpose. 

Script 1:

SELECT SCHEMA_NAME(tbl.[schema_id]) SchemaName,
		tbl.[name] AS TableName,
		idx.[name] AS IndexName,
		SUM(dps.[used_page_count]) * 8/1024 AS IndexSizeInMB
FROM sys.dm_db_partition_stats AS dps
		INNER JOIN sys.indexes AS idx ON dps.[object_id] = idx.[object_id]
									   AND dps.[index_id] = idx.[index_id]
		INNER JOIN sys.tables tbl ON tbl.[object_id] = idx.[object_id]
GROUP BY tbl.[name], idx.[name], tbl.[schema_id]
ORDER BY IndexSizeInMB DESC

Script 2:

SELECT OBJECT_SCHEMA_NAME(id) AS SchemaName,
	   OBJECT_NAME(id) AS TableName,
	   [name] AS IndexName,
	   dpages/128 AS IndexSizeInMB
FROM sysindexes
WHERE indid IN (1,2)
		AND FirstIAM IS NOT NULL
		AND objectproperty(id, 'IsUserTable') = 1

ORDER BY 4 DESC

Sample Output :