SQL Server管理索引时可用到的视图

从网上找到两个在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