T-SQL to get all Index details in SQL Server
Submitted by manjunathcbhat on Fri, 07/18/2014 - 10:41
Below is a small yet powerful script to get all the details you want to know about the Indexes in your database.
Simply run the below Code against the database for which you want to get the Index details.
select s.name as TABLE_SCHEMA, t.name as TABLE_NAME , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION from sys.key_constraints as k join sys.tables as t on t.object_id = k.parent_object_id join sys.schemas as s on s.schema_id = t.schema_id join sys.index_columns as ic on ic.object_id = t.object_id and ic.index_id = k.unique_index_id join sys.columns as c on c.object_id = t.object_id and c.column_id = ic.column_id order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, ORDINAL_POSITION;