Looking to write a SQL query which will list out all the indexes in your database as well as the columns in the index? Well my friend you’ve come to the right place. The query below will give list out the following information:
Schema
Table Name (or view)
Index Name
Is Primary Key
Is Unique Index
Column Name
Is Included Column
Column Order
The results will look something like this (click the image for a larger view):
Run the query and give it a go…
SELECT OBJECT_SCHEMA_NAME(ind.object_id) AS SchemaName , OBJECT_NAME(ind.object_id) AS ObjectName , ind.name AS IndexName , ind.is_primary_key AS IsPrimaryKey , ind.is_unique AS IsUniqueIndex , col.name AS ColumnName , ic.is_included_column AS IsIncludedColumn , ic.key_ordinal AS ColumnOrder FROM sys.indexes ind INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id INNER JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id INNER JOIN sys.tables t ON ind.object_id = t.object_id WHERE t.is_ms_shipped = 0 ORDER BY OBJECT_SCHEMA_NAME(ind.object_id) --SchemaName , OBJECT_NAME(ind.object_id) --ObjectName , ind.is_primary_key DESC , ind.is_unique DESC , ind.name --IndexName , ic.key_ordinal