Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

Indexes on a table

Discussion in 'SQL Server Scripts and Tools' started by Peter Schmitz, Dec 9, 2012.

  1. Peter Schmitz

    Peter Schmitz Administrator Staff Member

    Below is a small script that can be used to check whether or not a particular table in your database contains redundant indexes.

    By redundant indexes I mean that if there is one index on columns A and B, and a second index on columns A, B, and C, generally speaking, it would be sufficient to drop the first index and remove the second one.

    This script can help you identify such indexes, though I would suggest manually verifying the indexes anyway.

    Code (TSQL):

    DECLARE @tableName sysname;
    SET @tableName = 'myTable'; -- adjust for your own purposes

    SELECT @tableName, * FROM
    (
        SELECT i.name AS indexname, c.name AS columnname, ic.index_column_id FROM
            sys.indexes i
            JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
            JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
        WHERE
            i.object_id = OBJECT_ID(@tableName)
    ) AS Source
    PIVOT
    (
        MIN(columnname) FOR
        index_column_id IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16])
    ) AS PivotTable
    ORDER BY [1], [2], [3], [4], [5], indexname
    ;
     

Share This Page