Chris Miller Blog

So I wanted to start being a bit more aggressive about index management.  Specifically, I want to be able to find duplicated indexes inside of one of my vendor databases.  So (1) I can make fun of them even more than I do already, and (2) so I can give them a script of all their issues that they might be able to resolve them.

Here’s what I came up with.  Only really works if you have 19 or fewer columns per index.  Why 19?  I got tired of typing there.  You can write this up as a code generator if you don’t like it.  There are better ways to do this, but the way I’m doing it you can look at the temp table and use it to find the more subtle problem of a table with two indexes which are subsets of each other, which are usually but not always redundant.  Anyway, here ya go.

drop table #tablelist

select oname, index_id, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

into #TableList

from (

      select object_name(object_id) oname, index_id,

key_ordinal, Column_ID from sys.index_columns) AS SourceTable

      pivot (max(key_ordinal)

            for column_id in ([1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

)

) as PivotTable

select oname, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13],

[14], [15], [16], [17], [18], [19]

from #TableList

group by oname, [1], [2], [3], [4], [5], [6],

[7], [8], [9], [10], [11], [12], [13], [14],

[15], [16], [17], [18], [19]

having count(*) > 1

order by 1