Find Duplicate Indexes - Episode 1 (The Early SQL Versions)

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