List of All Indexes and Index Columns

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

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-index-columns-transact-sql?view=sql-server-2017