Do you need to write a SQL Server query to find out which columns an index is on? Do you have an index name but need to use T-SQL to determine the column names? Not a problem! The query below will qive you exactly what you want. The example uses an AdventureWorks index as the example so just replace it with your own index name and you’ll be in business.

Note: Need a query to show you all the indexes and columns in your database? You can hack the query below or just go to this post where I did the hacking for you: SQL Query: List All Indexes And Their Columns

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
        AND ind.name = 'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode' --your index name here
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

The result set will look something like this (click the image if you need a larger view):

That should do the trick!