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;
