查找和刪除重複索引

我的數據庫中有超過 1000 個表。在幾張桌子上，我發現了重複的索引。現在我想在數據庫中找到重複的索引並刪除它們。意思是，如果一個表在同一組列上有 2 個或多個索引，則刪除除一個之外的所有索引。

Brent Ozar 的 sp_BlitzIndex 將幫助您找到並辨識這些：

https://www.brentozar.com/blitzindex/sp_blitzindex-duplicate-indexes/

之後，您將不得不自己檢查這些索引並確定其中哪些已過時。這不是我會嘗試使用任何類型的腳本自動化的任務。

對於完全相同的副本，我一直在使用以下腳本：

-- find duplicate indexes
-- http://www.sqlservercentral.com/Forums/Topic1359723-392-1.aspx
-- marcelo miorelli
-- 31 jan 2013

set transaction isolation level read uncommitted

;WITH IndexColumns AS (
       SELECT DISTINCT a.object_id, a.name, 
                       a.type_desc, b.column_id, 
                       TABLE_NAME=c.name, 
                       [COL NAME]=d.name, 
                       b.is_included_column
       FROM sys.indexes a

       INNER JOIN sys.index_columns b 
               ON a.object_id = b.object_id AND a.index_id = b.index_id

       INNER JOIN sys.tables c 
               ON b.object_id = c.object_id

       INNER JOIN sys.columns d 
               ON c.object_id = d.object_id 
              AND b.column_id = d.column_id 

       WHERE is_hypothetical = 0

       ),
   CombineCols AS (

       SELECT object_id, name, type_desc, 
              table_name
             ,columns=STUFF((
               SELECT ',' + [COL NAME]
               FROM IndexColumns b
               WHERE a.object_id = b.object_id 
                 AND a.name = b.name 
                 AND a.type_desc = b.type_desc 
                 AND a.TABLE_NAME = b.TABLE_NAME 
                 AND b.is_included_column = 0
               ORDER BY [COL NAME]
               FOR XML PATH(''),TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')
           ,include_columns=ISNULL(STUFF((
               SELECT ',' + [COL NAME]
               FROM IndexColumns b
               WHERE a.object_id = b.object_id AND 
                   a.name = b.name AND 
                   a.type_desc = b.type_desc AND
                   a.TABLE_NAME = b.TABLE_NAME AND b.is_included_column = 1
               ORDER BY [COL NAME]
               FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, ''), '')
       FROM IndexColumns a
       GROUP BY object_id, name, type_desc, table_name)
SELECT b.type_desc, a.table_name, a.columns, a.include_columns, b.name
FROM (
   SELECT table_name, columns, include_columns
   FROM CombineCols
   GROUP BY table_name, columns, include_columns
   HAVING COUNT(name) > 1) a
INNER JOIN CombineCols b 
   ON a.table_name = b.table_name AND
       a.columns = b.columns  AND
       a.include_columns = b.include_columns
ORDER BY a.table_name, a.columns

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