List of indexes with Index Type – SQL Server

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 AS TableName, AS IndexName,
LOWER(i.type_desc) + CASE
WHEN i.is_unique = 1
THEN ‘, unique’
WHEN i.is_primary_key = 1
THEN ‘, primary key’
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, AS IndexedColumn,
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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s