SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL – Identify & Drop Duplicate Index

This post explains the simple steps to identify a duplicate indexes in all the databases.  The duplicate index are more than one indexes that differ only by it’s name but consist of identical fields internally. It generates overhead when performing updates, inserts or deletes, require lot of resources for rebuilds, take up space but are never used. Its always good cleanup.

Output of the below T-SQL are direct eligible items to delete. Please double check output before deleting any indexes.

Download the code here Duplicate_Index

Copy and paste the below code and execute it through SSMS


DECLARE @DBName [nvarchar] (128)
,@RowID [int]
,@LoopStatus [int]
,@DML varchar(max)

SET @RowID=1
SET @LoopStatus=1

DECLARE @DuplicateIndexFind TABLE
[table] [nvarchar](257) NULL,
[index] [sysname] NULL,
[exactduplicate] [sysname] NULL,
[DbName] varchar(100) NULL

DECLARE @DatabaseList TABLE ([RowNo] [smallint] identity (1, 1)
,[DBName] [varchar](200))

INSERT INTO @DatabaseList
SELECT ‘['+[name]+’]’ FROM [master].[sys].[databases] WITH (NOLOCK)
WHERE [state_desc] = ‘ONLINE’
AND [source_database_id] IS NULL
AND [database_id] > 4

WHILE @LoopStatus<>0
SELECT @DBName = [DBName]
FROM @DatabaseList WHERE [RowNo] = @RowID
SET @LoopStatus=0
SET @DML=’USE ‘+ @DBName +CHAR(13)+’;'+ ‘
with indexcols as
select object_id as id, index_id as indid, name,
select case keyno when 0 then NULL else colid end as [data()]
from ‘+ @DBName +’.sys.sysindexkeys’ +’ as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path(””)) as cols,
select case keyno when 0 then colid else NULL end as [data()]
from ‘+ @DBName +’.sys.sysindexkeys’ +’ as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path(””)
) as inc
from ‘+ @DBName +’.sys.indexes as i)
object_schema_name(c1.id) + ”.” + object_name(c1.id) as ”table”,
c1.name as ”index”,
c2.name as ”exactduplicate”
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc
order by object_schema_name(c1.id) + ”.” + object_name(c1.id)’

INSERT INTO @DuplicateIndexFind([table] ,[index],[exactduplicate]) exec (@DML)

update @DuplicateIndexFind
set DbName=@DBName
where DbName is NULL

SET @RowID=@RowID+1

select @@Servername ServerName,DbName DatabaseName,[Table],[INDEX],ExactDuplicate from @DuplicateIndexFind


Cross verify output using the below statement

sp_helpindex ‘pl_collect_def’



Thanks for reading my space.

Happy Learning!!

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.


Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...