從網上找到兩個在SQL Server管理索引時可用到的視圖:
從www.sqlservercentral.com找到發現可能沒有聚簇索引的表，沒有PK索引的表，冗余的索引，字段倒序排列的索引名稱：all_index_vIEw.sql
需要在每一個你想檢測的庫下建立這個視圖INDEXVIEW，然後調用裡面注釋了的select查詢語句來顯示結果。
SELECT 'Showing Tables with Missing Clustered index' AS Comments, I.*
FROM dbo.INDEXVIEW I
WHERE ClusterType = 'HEAP'
GO
SELECT 'Showing Tables with Missing Primary Keys' AS Comments, I.*
FROM dbo.INDEXVIEW I
LEFT OUTER JOIN dbo.INDEXVIEW I2
ON I.TableID = I2.TableID
AND I2.UniqueType = 'PRIMARY KEY'
WHERE I2.TableID IS NULL
GO
SELECT 'Showing Possible Redundant Index keys' AS Comments , I.*
FROM dbo.INDEXVIEW I
JOIN dbo.INDEXVIEW I2
ON I.TableID = I2.TableID
AND I.ColName1 = I2.ColName1
AND I.IndexName <> I2.IndexName
ORDER BY I.TableName,I.IndexName
GO
SELECT 'Showing Possible Reverse Index keys' AS Comments , I.*
FROM dbo.INDEXVIEW I
JOIN dbo.INDEXVIEW I2
ON I.TableID = I2.TableID
AND I.ColName1 = I2.ColName2
AND I.ColName2 = I2.ColName1
AND I.IndexName <> I2.IndexName
GO
還有一個類似功能的視圖:
從www.sql-server-performance.com找到可能冗余的索引：find_dup_index_vIEw.sql
也是需要在每一個你想檢測的庫下建立這個視圖vw_index_list,然後一到兩個月運行一下，找找冗余的索引。
SELECT table_name,constraint_name, indexname, constraint_type
FROM information_schema.table_constraints c
INNER JOIN vw_index_list i ON c.constraint_name = i.indexname
