Listing 3: Using FOR XML PATH with the STUFF Function to Concatenate the Names of Columns in Multicolumn Indexes

SELECT ic2.[object_id], index_name = i.name, ic2.index_id, STUFF(
  (SELECT ',' + c.name AS [text()]
    FROM sys.index_columns ic
    JOIN sys.columns c ON ic.[object_id] = c.[object_id] 
     AND ic.column_id = c.column_id
    WHERE ic.[object_id] = ic2.[object_id] 
     AND ic.index_id = ic2.index_id
    ORDER BY ic.key_ordinal
    FOR XML PATH('')
  ), 1, 1, '') AS column_list
FROM sys.index_columns ic2
JOIN sys.indexes i ON ic2.[object_id] = i.[object_id] 
  AND ic2.index_id = i.index_id
WHERE ic2.[object_id] = OBJECT_ID('SalesLT.Address')
GROUP BY ic2.[object_id], i.name, ic2.index_id;