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