Search This Blog

Monday, March 7, 2011

SQL DBA - List unused indexes

SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] ,

i.name

FROM sys.indexes AS i

INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]

WHERE i.index_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() )

AND o.[type] = 'U'

ORDER BY OBJECT_NAME(i.[object_id]) ASC

No comments: