Find duplicate indexes

-- Find indexes that are indexed on the same columns for any 
-- given table and hence are duplicates.

-- set no count on
set nocount on

-- create some temp tables to hold tempoery data
if exists(select * from tempdb.dbo.sysobjects 
           where id = object_id('tempdb.dbo.#temp_ch'))
   drop table #temp_ch

create table #temp_ch(
       id int null,
       indid int null,
       cols varchar(128) null)

if exists(select * from tempdb.dbo.sysobjects 
           where id = object_id('tempdb.dbo.#temp_ch1'))
   drop table #temp_ch1

create table #temp_ch1(
       id int null,
       indid int null,
       cols varchar(128) null)

-- declare some variables & set initial conditions
declare @uqid varchar(20),
        @cols varchar(128)

set @cols = ''

-- Load the temp table with a joined list from sysobjects and sysindexes
insert into #temp_ch1(
       id, 
       indid)
select a.id, a.indid 
  from sysindexes a 
  join sysobjects b 
    on a.id = b.id 
   and b.xtype = 'U'
   and a.indid <> 0 
   and a.indid <> 255
   and a.status&32 = 0

-- get the first unique id
select @uqid = min(ltrim(str(id))+ltrim(str(indid)))
  from #temp_ch1

-- loop for column data
while @uqid is not null
   begin
      -- get the column id list for this index
      select @cols = @cols + ltrim(str(colid)) + ','
       from sysindexkeys 
      where ltrim(str(id))+ltrim(str(indid)) = @uqid

      -- remove the last comma
      select @cols = substring(@cols, 1, len(@cols)-1)
     
      -- add the column id list to the temp table
      update #temp_ch1 
         set cols = @cols 
       where ltrim(str(id))+ltrim(str(indid)) = @uqid

      -- reset the variable
      set @cols = ''
     
      --get the next unique id
      select @uqid = min(ltrim(str(id))+ltrim(str(indid)))
        from #temp_ch1 
       where ltrim(str(id))+ltrim(str(indid)) > @uqid
   end

-- remove all entries were the group count is not greater than 1
-- leaving only duplicates & some a few other matches by id
delete from #temp_ch1
 where id not in 
         (select id  
            from #temp_ch1
           group by id, cols
          having count(cols) > 1)

-- be sure the temp table is empty
truncate table #temp_ch

-- now do a self join to get only duplicates
insert into #temp_ch 
select a.* 
  from #temp_ch1 a 
  join #temp_ch1 b
    on a.id = b.id 
   and a.cols = b.cols 
   and a.indid <> b.indid

-- display the results
set nocount off
select b.id 'Table id' ,
       b.indid 'Index id',
       case when b.status&2048 = 2048 then 'PKI ' else 'STD' end 'Type',
       case when (b.indid = 1 or b.indid = 256) then 'Y' else 'n' end 'Clustered',
       convert(varchar(70), f.name + '.' + a.name + '.' + b.name ) 'Table.Index', 
       convert(varchar(15),e.cols) 'Columns id(s)'
  from sysobjects a 
  join sysusers f
    on a.uid = f.uid
  join sysindexes b
    on a.id = b.id
   and a.xtype = 'U'
   and b.status&32 = 0
   and b.indid <> 0 
   and b.indid <> 255
  join #temp_ch e
    on b.id = e.id
   and b.indid = e.indid
 order by a.name, b.name
go