Command to find Index name & Size of Index

Below Command will gives you output of Index name , Index type.

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U' and o.name='Table_name'
ORDER BY o.name, i.type

OUTPUT:-

--------------------------------------------------------------------------------------------------------------------------

Below Command will gives you output of Index name , Size of Index .

SELECT

OBJECT_NAME(i.OBJECT_ID) AS TableName,

i.name AS IndexName,

i.type_desc as index_type,

i.index_id AS IndexID,

SUM(a.used_pages)/128 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)='EPOEventsMT'

GROUP BY i.OBJECT_ID,i.index_id,i.name,i.type_desc

--ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

order by SUM(a.used_pages)/128  desc 

OUTPUT:-