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.

SQL SERVER - Query Listing All the Indexes Key Column with Included Column indexcolumns

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.

Solarwinds
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.

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Update Table Statistics in Parallel with FULLSCAN
Next Post
SQL SERVER – How to Drop All the Hypothetical Indexes of Database?

Related Posts

5 Comments. Leave new

Leave a Reply

Menu