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. 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.
List of Indexes from a database
SELECT t.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
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
Including INFORMATION_SCHEMA
SELECT t.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
c.name AS IndexedColumn,
ic.is_included_column,
db.TABLE_SCHEMA,
db.TABLE_CATALOG
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
INNER JOIN INFORMATION_SCHEMA.tables db On t.name = db.TABLE_NAME
Hope you find this article helpful.
Big Data and SQL 