The following query returns each table in your database, listing out all the indexes, and for each index, a list of its columns. You can uncomment the where clause to determine the list of all indexes and its columns of any particular object.
This query is useful for a database developer to analyse what indexes are on a table and the columns referenced to them before adding the new ones.
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS 'SchemaName', OBJECT_NAME(i.object_id) AS 'TableName', i.name AS 'IndexName', i.type_desc AS 'IndexType', CASE WHEN (i.[is_primary_key]) = 1 THEN 'PRIMARY KEY' WHEN (i.[is_unique]) = 1 THEN 'UNIQUE' WHEN (i.[is_unique]) = 0 THEN 'NON-UNIQUE' ELSE '' END AS 'ConstraintType', STUFF (--get a list of columns (SELECT ', ' + COL_NAME(ic.object_id, ic.column_id) FROM sys.Index_columns ic WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id ORDER BY index_column_id ASC FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') AS 'ColumnList' FROM sys.indexes i WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 AND i.name IS NOT NULL --AND object_name(i.Object_ID) = 'TAT_TIME' ORDER BY 1,2
References