The system catalog views sys.indexes and sys.index_columns make it easy to get a list of all indexes and index columns in a database. You may get all the indexes for tables, views, and table valued functions by using the sys.indexes function.
The following query is used to acquire a list of a specific database’s indexes including the index type and index description. The name of the table, the index name, the type of the index, and lastly the name and the type of the key and non-key columns included in these indexes are all included in the output.
Retrieve Index List
SELECT t.name AS TableName,
i.name AS IndexName,
LOWER(i.type_desc) + CASE
WHEN i.is_unique = 1
THEN ‘, unique’
ELSE ”
END + CASE
WHEN i.is_primary_key = 1
THEN ‘, primary key’
ELSE ”
END AS ‘index_description’,
CASE WHEN i.[type] = 1 THEN ‘Clustered index’
WHEN i.[type] = 2 THEN ‘Nonclustered unique index’
WHEN i.[type] = 3 THEN ‘XML index’
WHEN i.[type] = 4 THEN ‘Spatial index’
WHEN i.[type] = 5 THEN ‘Clustered columnstore index’
WHEN i.[type] = 6 THEN ‘Nonclustered columnstore index’
WHEN i.[type] = 7 THEN ‘Nonclustered hash index’
end AS index_type,
c.name AS IndexedColumn,
ic.is_included_column
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c ON i.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.tables AS t ON i.object_id = t.object_id
Hope you find this article helpful.
Big Data and SQL 