SQL 2005 查找重複索引

我創建了以下腳本來提取數據庫上的重複索引，但我需要找到一種方法在 SQL 伺服器上的所有數據庫中實現它 - 有什麼建議嗎？

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;

我會避免 msforeachdb 它已知會錯過我經歷過的第一人稱的數據庫。

要麼創建自己的游標（學習新東西的機會！），要麼使用 Aaron Bertrand 的可靠版本。sp_ineachdb

我同意其他人的觀點，即 msforeachdb 有問題。使用 Aaron Bertrand 的sp_foreachdb，您應該能夠使用以下範例：

（問號

$$ ? $$基本上就像每個列舉數據庫的佔位符一樣。）

EXEC sp_foreachdb @command = N'
use ?;
select ''?'';
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;
'
   ,@suppress_quotename = 1
   ,@user_only = 1;

引用自：https://dba.stackexchange.com/questions/158579