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

Rate

4 (1)

Share

Share

Rate

4 (1)