Get the Index name form the indexID SQL Server

select name, index_id from sys.indexes where object_id = object_id(‘Index name’)

List all indexes with size and table name – TSQL

select
object_name(i.object_id) as TableName,
i.name as IndexName,
i.index_id as IndexID,
8 * SUM(a.used_pages) as ‘Indexsize(KB)’
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 i.type_desc <> ‘heap’
Group by i.object_id,i.index_id,i.name
Order By object_name(i.object_id),i.index_id

List all Indexes with fragmentation percent in a Database – TSQL

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(‘DB_name’), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30 and index_type_desc <> ‘heap’
ORDER BY indexstats.avg_fragmentation_in_percent DESC,TableName