SELECT OBJECT_NAME(I.object_id) as objectname,
I.name AS indexname,
I.index_id AS indexid
FROM sys.indexes AS I
INNER JOIN sys.objects AS O
ON O.object_id = I.object_id
WHERE O.object_id > 100
AND I.type_desc = 'NONCLUSTERED'
AND I.type_id NOT IN
(SELECT S.index_id
FROM sys.dm_db_index_usage_stats AS S
WHERE S.object_id = I.object_id
AND I.index_id = S.index_id
AND database_id = DB_ID('TSQL2012'))
ORDER BY objectname, indexname
*finds non clustered indexes not used from the last restart of the instance
No comments:
Post a Comment