Display the list of indexes and columns for a given table

You might find yourself in a situation where you have to analyze the indexes for a given table and want to know which columns are part of which index. Such a view can help you quickly analyze which indexes are reduntant and can probably be dropped.

Use the below code to accomplish that. This code gives you the result set as mentioned below:

1. Rows as the list of indexes. 

2. Columns as the list of columns in that table 

3. Result set in each row displays 1 for each column that is included in that index, else displays 0

DECLARE @Columns NVARCHAR(MAX), @SQL NVARCHAR(MAX);

-- Step 1: Construct the list of columns for the dbo.Associate table using FOR XML PATH

SELECT @Columns = STUFF((

    SELECT ', MAX(CASE WHEN c.name = ''' + c.name + ''' THEN 1 ELSE 0 END) AS ' + QUOTENAME(c.name)

    FROM sys.columns c

    JOIN sys.tables t ON c.object_id = t.object_id

    WHERE t.name = 'TABLENAME' AND t.schema_id = SCHEMA_ID('dbo') --Replace TABLENAME here

    FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- Step 2: Construct the dynamic SQL query

SET @SQL = '

SELECT

    i.name AS IndexName, ' + @Columns + '

FROM

    sys.indexes i

    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id

    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id

    JOIN sys.tables t ON t.object_id = i.object_id

WHERE

    t.name = ''Associate'' AND t.schema_id = SCHEMA_ID(''dbo'')

GROUP BY

    i.name

ORDER BY

    i.name';

-- Step 3: Execute the dynamic SQL query

EXEC sp_executesql @SQL;