Retornar Índices Duplicados


 

Estes dois scripts (sql200 e sql2005/2008) são bem úteis no dia a dia para verificarmos indices duplicados.

 

————————————————————————

— Name : FindDuplicateIndexes.sql

— Author : Jeff Weisbecker December 4, 2003

— RDBMS : SQL Server 2000 and 7.0

— Desc : This SQL statement will find duplicate indexes on tables.

————————————————————————

SELECT OBJECT_NAME(i1.id) AS ‘Table’,

i1.name AS ‘Index’,

i2.name AS ‘Duplicate Index’

FROM sysindexes i1,

sysindexes i2

WHERE i1.indid NOT IN (0,255)

AND i2.indid NOT IN (0,255)

AND INDEXPROPERTY(i1.id, i1.name, ‘IsStatistics’) = 0

AND INDEXPROPERTY(i2.id, i2.name, ‘IsStatistics’) = 0

AND i1.id = i2.id

AND i1.indid < i2.indid

AND NOT EXISTS (SELECT ‘1’

FROM sysindexkeys ik1,

sysindexkeys ik2

WHERE ik1.id = i1.id

AND ik1.id = ik2.id

AND ik1.indid = i1.indid

AND ik2.indid = i2.indid

AND ik1.keyno = ik2.keyno

AND ik1.colid != ik2.colid)

———————————————————————-

— Comment out the following condition if you would like to consider

— indexes with a different number of columns as duplicates as long

— as the larger index shares the same starting sequence as the other

— index.

———————————————————————-

AND 0 = (SELECT MAX(ik1.keyno) – MAX(ik2.keyno)

FROM sysindexkeys ik1,

sysindexkeys ik2

WHERE ik1.id = i1.id

AND ik1.id = ik2.id

AND ik1.indid = i1.indid

AND ik2.indid = i2.indid)

no 2005 /2008 uso esta

— exact duplicates
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 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 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 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;

The second variation of this query finds partial, or duplicate, indexes that share leading key columns, e.g. Ix1(col1, col2, col3) and Ix2(col1, col2) would be considered duplicate indexes. This query only examines key columns and does not consider included columns.
These types of indexes are probable dead indexes walking.

— Overlapping indxes
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 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
from sys.indexes as i
)
select
object_schema_name(c1.id) + ‘.’ + object_name(c1.id) as ‘table’,
c1.name as ‘index’,
c2.name as ‘partialduplicate’
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and (c1.cols like c2.cols + ‘%’
or c2.cols like c1.cols + ‘%’) ;

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in SQL SERVER EM GERAL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s