Find Duplicate Indexes

,

There are plenty of scripts out there that find duplicate indexes, but they all seem to use cursors.  I didn't want to use a cursor so I ended up creating a few that could handle the duplicates and decided to share this one. 

This will return the table name and the names of the two identical indexes.  If you would like to return indexes with a different number of columns, but the same sequence of columns then comment out the last condition in the SQL statement.  For example, if you would like to return Index #1 as a duplicate of Index #2.

Index #1
  Column 2
  Column 3

Index #2
  Column 2
  Column 3
  Column 4

------------------------------------------------------------------------
--  Name   :  FindDuplicateIndexes.sql  
--  Author :  Jeff Weisbecker  December 4, 2003
--  RDBMS  :  SQL Server 2000 and 7.0
--  Desc   :  This SQL statement will find duplicate indexes on tables.
--            
------------------------------------------------------------------------
SELECT  OBJECT_NAME(i1.id)     AS  'Table',
        i1.name                AS  'Index',
        i2.name                AS  'Duplicate Index'
FROM sysindexes    i1,
     sysindexes    i2
WHERE i1.indid NOT IN (0,255)
  AND i2.indid NOT IN (0,255)
  AND INDEXPROPERTY(i1.id, i1.name, 'IsStatistics') = 0
  AND INDEXPROPERTY(i2.id, i2.name, 'IsStatistics') = 0
  AND i1.id    = i2.id
  AND i1.indid < i2.indid
  AND NOT EXISTS (SELECT '1'
                  FROM sysindexkeys  ik1,
                       sysindexkeys  ik2
                  WHERE ik1.id     = i1.id
                    AND ik1.id     = ik2.id
                    AND ik1.indid  = i1.indid
                    AND ik2.indid  = i2.indid
                    AND ik1.keyno  = ik2.keyno
                    AND ik1.colid != ik2.colid)
  ----------------------------------------------------------------------
  --  Comment out the following condition if you would like to consider
  --  indexes with a different number of columns as duplicates as long
  --  as the larger index shares the same starting sequence as the other
  --  index.
  ----------------------------------------------------------------------
  AND 0 =        (SELECT  MAX(ik1.keyno) - MAX(ik2.keyno)
                  FROM sysindexkeys  ik1,
                       sysindexkeys  ik2
                  WHERE ik1.id     = i1.id
                    AND ik1.id     = ik2.id
                    AND ik1.indid  = i1.indid
                    AND ik2.indid  = i2.indid)

Rate

5 (4)

Share

Share

Rate

5 (4)