Find duplicate indexes

Two SQL Server 2000 user-defined functions that return the duplicate indexes on the current database.

This script returns duplicate indexes on the current database. It uses two user-defined functions that are compatible with SQL Server 2000 (I used SP3). It won't work in SQL Server 7.0 because of the user-defined functions and a memory table.

 /*
Find Duplicate Indexes  
This script returns the duplicate indexes on any database. 
Created by G.R. Preethiviraj Kulasingham B.Sc., MCP   
Written on  : February  20, 2003    
Modified on : May 22, 2003    
*/

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) 
Returns Varchar(2000) 
As
BEGIN
     Declare @SQL varchar(2000)
     Set @SQL =''
     Select @SQL= @SQL +','+  convert(varchar(5),
((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 = SUbString(@SQL, 2, LEN(@SQL)-1)
     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) 
Returns Varchar(8000) 
As
BEGIN
     Declare @SQL varchar(8000)
     Set @SQL =''
     Select @SQL= @SQL +','+ 
INDEX_Col(Object_name(@TableID), @indid, keyno)  
from sysindexkeys 
     Where id =@Tableid and indid=@Indid 
     Order by id, indid, Keyno 
     IF LEN(@SQL)>1
          Select @SQL = SUbString(@SQL, 2, LEN(@SQL)-1)
     Return @SQL
END
GO

Declare @TempTable Table (
ID int ,
Indid int,
ColID Varchar(2000),
ColNames Varchar(8000)
)
Insert into @TempTable 
Select Id, indid, dbo.GetColid(id, indid), dbo.GetColList(id, indid) 
from Sysindexes order by id 
Delete @TempTable Where ColNames=''

Select O.Name 'Table', I.Name 'Index', B.ColNames 'Column(s)' 
From SysObjects O, Sysindexes I, @TempTable B, 
(Select id, ColID, Count(Indid) as inst from  @TempTable 
Group By id, ColID, ColNames
HAving Count(indid)>1) A
Where A.id = B.id and A.ColID= B.colID and 
B.id =i.id and B.indid = i.indid and 
i.id = o.id

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.

This was first published in February 2005

Dig deeper on SQL Server Database Modeling and Design

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close