SQL Server列出未使用的Index

SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
       OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
       I.NAME AS IndexName,SUM(a.used_pages)*8 AS 'Indexsize(KB)'
FROM sys.indexes I
JOIN sys.partitions AS p ON p.OBJECT_ID = I.OBJECT_ID AND p.index_id = I.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE
-- only get indexes for user created tables
OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1
-- ###find all indexes that exists but are NOT used
AND NOT EXISTS ( 
SELECT index_id 
     FROM sys.dm_db_index_usage_stats
     WHERE OBJECT_ID = I.OBJECT_ID 
     AND I.index_id = index_id 
     -- limit our query only for the current db
     AND database_id = DB_ID())
GROUP BY I.OBJECT_ID,I.index_id,I.name
ORDER BY SchemaName, ObjectName, IndexName


參考：
http://caryhsu.blogspot.tw/2012/12/blog-post.html