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
(
[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
BEGIN
SELECT @DBName = [DBName]
FROM @DatabaseList WHERE [RowNo] = @RowID
IF @@ROWCOUNT=0
BEGIN
SET @LoopStatus=0
END
ELSE
BEGIN
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)
select
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(
update @DuplicateIndexFind
set DbName=@DBName
where DbName is NULL
SET @RowID=@RowID+1
END
END
select @@Servername ServerName,DbName DatabaseName,[Table],[INDEX],ExactDuplicate from @DuplicateIndexFind
*************************************************************
Cross verify output using the below statement
sp_helpindex ‘pl_collect_def’
Output:-
Thanks for reading my space.
Happy Learning!!
