Find Duplicate Indexes - Episode 1 (The Early SQL Versions)
The excellent script contributed by G.R. Preethiviraj Kulasingham (Preethi) Sri Lanka (contributed 2/20/03, modified 5/22/03) identifies duplicate indexes for the database on which the script is run. It requires User Defined Functions, a feature restricted to SQL Server 2000.
This version achieves similar results without using UDFs or creating any other permanent objects. It therefore works on earlier SQL Server versions. In addition, it ignores statistics records coexisting in the sysindexes table.
/*
sqryIndentifyDuplicateIndexes
Created by Larry Ansley 5/28/03.
*/
Declare @TableID Int, @IndexID Int, @ColumnID Int, @KeyNo Int,
@KeyString VarChar(2000), @NameString VarChar(2000)
Set @TableID = 0
Set @IndexID = 0
Set @KeyNo = 0
-- Temporary table to accumulate KeyString for all indexes
Create Table #Indexes (TableID Int, IndexID Int, KeyString VarChar(2000), NameString VarChar(2000))
-- Table Loop
While Exists
(Select * From sysobjects Where id > @TableID)
Begin
Set @TableID = (Select Min(id) From sysobjects Where id > @TableID)
-- Index Loop
While Exists
(Select *
From sysindexes i
Where i.id = @TableID
and i.indid > @IndexID
and i.dpages > 0)
Begin
Set @IndexID =
(Select Min(i.indid)
From sysindexes i
Where i.id = @TableID
and i.indid > @IndexID
and i.dpages > 0)
-- Key Loop
While Exists
(Select *
From sysindexkeys
Where id = @TableID
and indid = @IndexID and keyno > @KeyNo)
Begin
Set @KeyNo = (Select Min(keyno) From sysindexkeys Where id = @TableID
and indid = @IndexID and keyno > @KeyNo)
Set @ColumnID = (Select colid From sysindexkeys Where id = @TableID
and indid = @IndexID and keyno = @KeyNo)
Set @KeyString = IsNull(@KeyString + ',', '') -- IsNull to eliminate initial ','
+ Cast(@TableID as VarChar(10)) + ','
+ Cast(-@ColumnID *
((indexkey_property(@TableID, @IndexID, @KeyNo, 'isdescending')* 2)- 1)
as VarChar(10))
Set @NameString = IsNull(@NameString + ', ', '') -- IsNull to eliminate initial ','
+ (Select name From syscolumns Where id = @TableID and colid = @ColumnID)
+ Case indexkey_property(@TableID, @IndexID, @KeyNo, 'isdescending')
When 0 Then '' Else ' Desc' End
End
Insert #Indexes Values(@TableID, @IndexID, @KeyString, @NameString)
-- Reset @KeyNo & @KeyString
Set @KeyNo = 0
Set @KeyString = Null
Set @NameString = Null
End
-- Reset @IndexID
Set @IndexID = 0
End
-- Report the results.
Select o.name as 'Table Name',
i.name as 'Index Name',
Case i.indid
When 1 Then 'Clustered'
Else 'Non-Clustered' End as 'Index Type',
#i.NameString as 'Key Columns'
From #Indexes #i
Join sysindexes i
on #i.TableID = i.id
and #i.IndexID = i.indid
Join sysobjects o
on #i.TableID = o.id
Where #i.KeyString In
(Select KeyString
From #Indexes
Group By KeyString
Having Count(*) > 1)
Order By o.name, #i.KeyString
Drop Table #Indexes
