SQL SERVER – Query Listing All the Indexes Key Column with Included Column

Today’s blog post is directly inspired by the conversation I had during my Comprehensive Database Performance Health Check. During the consulting engagement, we identified the customers had too many indexes on tables. The goal was to consolidate the indexes and to do the same, we realized that we needed a script which lists all the key columns and included columns of the indexes.

Fortunately, I had done similar tasks before and I was able to quickly help my customers to consolidate the indexes. Meanwhile, here is the Query Listing All the Indexes Key Column with Included Column.

SELECT
	QUOTENAME(SCHEMA_NAME(t.schema_id)) AS SchemaName,
    QUOTENAME(t.name) AS TableName,
    QUOTENAME(i.name) AS IndexName,
	i.type_desc,
    i.is_primary_key,
    i.is_unique,
    i.is_unique_constraint,
    STUFF(REPLACE(REPLACE((
        SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()]
        FROM sys.index_columns AS ic
        INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
        ORDER BY ic.key_ordinal
        FOR XML PATH
    ), '<row>', ', '), '</row>', ''), 1, 2, '') AS KeyColumns,
    STUFF(REPLACE(REPLACE((
        SELECT QUOTENAME(c.name) AS [data()]
        FROM sys.index_columns AS ic
        INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
        ORDER BY ic.index_column_id
        FOR XML PATH
    ), '<row>', ', '), '</row>', ''), 1, 2, '') AS IncludedColumns,
    u.user_seeks,
    u.user_scans,
    u.user_lookups,
    u.user_updates
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS u ON i.object_id = u.object_id AND i.index_id = u.index_id
WHERE t.is_ms_shipped = 0
AND i.type <> 0

I have actually received the above script from one of my Performance Tuning Clients only. He had kindly shared the script and suggested that I can use it for my other clients who are struggling with SQL Server Performance.

Here are the additional blog posts which are associated which are related to the same.

• Why is SQL Server Consolidation Better Than Having a Scattered Environment?
• SQL Server – Update Table Statistics in Parallel with FULLSCAN
• SQL SERVER – New Parallel Operation Cannot be Started Due to Too Many Parallel Operations Executing at this Time

• What is Consolidation of Index? – Interview Question of the Week #212

I hope you have found this blog post which lists key columns and included columns of the indexes useful.

Reference: Pinal Dave (https://blog.sqlauthority.com)