SQL Server 2008 Index Usage

The following code can be used to determine index usage. It may then be possible to drop unnecessary indexes.

SELECT object_name(s.[object_id]) as [object name] ,i.[name] as [index name] ,user_seeks ,user_scans ,user_lookups ,user_updates FROM sys.dm_db_index_usage_stats as s INNER JOIN sys.indexes as i on i.[object_id] = s.[object_id] AND i.index_id = s.index_id WHERE s.database_id = db_id('dbname') AND objectproperty(s.[object_id],'IsUserTable') = 1 AND s.object_id = object_id('dbo.table_name')