Technical Article

Find Duplicate Indexes (Updated)

,

This script returns the Duplicate indexes on any database (if any).

Compatible with SQL Server 2000 only.

Latest update Checks for possible duplications and removes them. Further, additional fields are displayed to support decision making.

/*
 
 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
*/

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating