This script returns duplicate indexes on the current database. It uses two user-defined functions that are compatible...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.