Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

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
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([table] ,[index],[exactduplicate]) exec (@DML)

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:-

duplicate_index

Thanks for reading my space.

Happy Learning!!


Comments

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

Loading comments...