SQL query to get columns of a composite index

Question

The following query returns the columns of a composite index in multiple rows.

Question: how can we write a query that returns all columns of a composite index in one row?

Example:

create table #t
(
    c int, 
    d varchar(5), 
    e varchar(10)
)

create index idx_test on #t (d, e)

Running the following query on tempdb returns two rows for the same index. I would like it to return one row under header column_names containing the columns d and e separated by , such as e,d

SELECT 
    i.name AS index_name,
    COL_NAME(ic.object_id, ic.column_id) AS column_names
FROM 
    sys.indexes AS i
INNER JOIN 
    sys.index_columns AS ic ON i.object_id = ic.object_id 
                            AND i.index_id = ic.index_id
WHERE 
    i.object_id = OBJECT_ID('#t');

Output:

index_name	column_names
idx_test	d
idx_test	e

Desired output:

|      index_name     |     column_names |
|---------------------|------------------|
|          idx_test   |         d,e      |

Answer 1

You just need to use STRING_AGG in your query and group by the index name - try this code:

SELECT 
    i.name AS index_name,
    STRING_AGG(COL_NAME(ic.object_id, ic.column_id), ', ') AS column_names
FROM 
    sys.indexes AS i
INNER JOIN 
    sys.index_columns AS ic ON i.object_id = ic.object_id 
                            AND i.index_id = ic.index_id
WHERE 
    -- updated to keep siggemannen happy ;-) 
    i.object_id = OBJECT_ID('tempdb..#t')
    -- or alternatively: use the known index name; there could be more than 1 index on that table ...
    -- i.name = 'idx_test'
GROUP BY
    i.name;