Find Duplicate Indexes (Updated)

/*
 
 Find Duplicate Indexes

 This script returns the Duplicate indexes on any database
 I use 2 User defined Functions
 Compatible with SQLServer 2000 (I used sp3)
 It won't work in SQL Server 7.0 because It uses user-defined functions and a Memory.


 Created by G.R. Preethiviraj Kulasingham
pkulasingham@virtusa.com
 Written on  : February  20, 2003
 Modified on : May 18, 2004
Additional code written to remove duplicate entries.
Additional code to include index properties.
*/
IF EXISTS(Select id from sysobjects Where id = object_id('dbo.GetColID'))
   DROP FUNCTION dbo.getColID
GO

Create Function dbo.getColID (@TableID int, @indid int) 
/*
Parameters:
@TableID: ID of the Table
@IndID  : ID of the Index
Returns All the Columns (ID) for the given index in string format seperated by '&'sign. '-' is added for descending keys
*/
Returns Varchar(200) 
As
BEGIN
     Declare @SQL varchar(200)
     Set @SQL =''
     Select @SQL= @SQL +'&'+  convert(varchar(7),((indexkey_property(id, indid, keyno, 'isdescending')*-2)+1)* colid)   from sysindexkeys
     Where id =@Tableid and indid=@Indid 
     Order by id, indid, Keyno 
     IF LEN(@SQL)>1
          Select @SQL = @SQL +'&'
     Return @SQL 
END
GO


IF EXISTS(Select id from sysobjects Where id = object_id('dbo.GetColList'))
   DROP FUNCTION dbo.getColList

GO

Create Function dbo.getColList (@Tableid int, @indid int) 
/*
Parameters:
@TableID: ID of the Table
@IndID  : ID of the Index
Returns Index Key (column names) for the given index in string format seperated by commas.
*/
Returns Varchar(8000) 
As
BEGIN
     Declare @SQL varchar(8000)
     Set @SQL =''
     Select @SQL= @SQL +', '+ INDEX_Col(User_name(ObjectProperty(@TableID, 'Ownerid'))+'.'+Object_name(@TableID), @indid, keyno)+
     Case indexkey_property(id, indid, keyno, 'isdescending') When 1 Then '(Desc)' Else '' end   from sysindexkeys 
     Where id =@Tableid and indid=@Indid 
     Order by id, indid, Keyno 
     IF LEN(@SQL)>2
          Select @SQL = SUbString(@SQL, 3, LEN(@SQL)-2)
     Return @SQL
END
GO


/*
@TempTable is used to store the keys in long string format
*/
Declare @TempTable Table (
ID int ,
Indid int,
ColID Varchar(200),
Status int
)

Declare @Duplicates Table (
LineID int Identity(1,1),
ID int ,
hasClustered char(3) not null default('NO'),
Indid1 int,
ColID1 Varchar(200),
Status1 int, 
Desc1 varchar(200),
IndID2 int,
ColID2 Varchar(200),
Status2 int,
Desc2 varchar(100)
)

Insert into @TempTable  
Select Id, indid, dbo.GetColid(id, indid), status  from Sysindexes 
where (status & 64)=0  
order by id 
Delete @TempTable Where ColID='' 


Insert into @Duplicates (ID, IndID1, ColID1, Desc1, Status1, IndID2, ColID2, desc2, status2 )
Select A.ID, A.IndID,  A.ColID, '', A.status, B.IndID,  B.ColID, '', B.status
from @Temptable A, @TempTable B 
Where A.id = b.id and a.indid<>b.indid and 
     a.colid like b.colid 
+'%' 


--This part removes the duplicate entries.

Delete @Duplicates Where LineID In (
Select A.LineID from @Duplicates A, @Duplicates B
Where A.ID = B.ID and A.IndID1= B.IndID2 and A.IndID2= B.IndID1 and  A.LineID>B.LineID)
Delete @Duplicates Where LineID In (
Select A.LineID from @Duplicates A, @Duplicates B
Where A.ID = B.ID and  A.IndID1 = B.IndID2 )

-- Identify the index properties

Update @Duplicates Set Desc1 =CASE status1 & 16 WHEN 0 THEN 'Nonclustered' ELSE 'Clustered' END
Update @Duplicates Set Desc2 =CASE status2 & 16 WHEN 0 THEN 'Nonclustered' ELSE 'Clustered' END



Declare @Desc varchar(20), @Number int
Declare spt_Vals Cursor  
FOR
Select name, number  from master.dbo.spt_Values 
Where type ='I' and number in (1,2, 4, 32, 2048, 4096)  
Order by number 
Open  spt_vals
FETCH Next from spt_vals into @Desc, @Number 
WHILE @@FETCH_STATUS=0
  BEGIN
Update @Duplicates Set Desc1 = Desc1 + ', '+ @Desc  
where status1 & @number <>0
Update @Duplicates Set Desc2 = Desc2 + ', '+ @Desc  
where  status2 & @number <>0
FETCH Next from spt_vals into @Desc, @Number 
 END

CLOSE spt_Vals
DEALLOCATE spt_vals
Update @Duplicates Set  Desc1 = replace(Desc1, 'unique, primary key', 'primary key'),  
Desc2 = replace(Desc2, 'unique, primary key', 'primary key')

Update @Duplicates Set  Desc1 = replace(Desc1, 'unique, unique key', 'unique key'),  
Desc2 = replace(Desc2, 'unique, unique key', 'unique key')

-- Identify whether table has clustered index 
Update @Duplicates Set HasClustered = 'YES' Where id in (
Select id From sysIndexes Where IndId=1)

--Update @Duplicates Set HasClustered = 'NO' Where id in (
--Select id From sysIndexes Where IndId=0)



Select User_name(ObjectProperty(A.ID, 'Ownerid'))+'.'+Object_name(a.id) 'Table Name', HasClustered, 
IA.Name 'Index 1', dbo.GetColList(A.id, A.indid1) 'Keys of Index 1',  A.Desc1 'Desc 1',
IB.Name 'Index 2', dbo.GetColList(A.id, A.indid2) 'Columns of Index 2', A.Desc2 'Desc 2'
from @Duplicates A, SysIndexes IA, Sysindexes IB
Where IA.id =A.id and IA.indId = A.IndID1 and IB.ID = A.ID and IB.indId=A.IndID2 
order by User_name(ObjectProperty(A.ID, 'Ownerid'))+'.'+Object_name(a.id)

/*

GO
DROP FUNCTION dbo.getColList

GO
DROP FUNCTION dbo.getColID
GO
*/