Script to see index usage stats

SELECT object_name(s.object_id) AS ObjectName

, s.object_id

, i.name as IndexName

, i.index_id

, user_seeks

, user_scans

, user_lookups

, user_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i

ON i.object_id = s.object_id

AND i.index_id = s.index_id

WHERE database_id = db_id ()

AND objectproperty(s.object_id,'IsUserTable') = 1

ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC

-- Possible bad Indexes (writes > reads)

DECLARE @dbid int

SELECT @dbid = db_id()

SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,

'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,

'Difference' = user_updates - (user_seeks + user_scans + user_lookups)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

ON s.object_id = i.object_id

AND i.index_id = s.index_id

WHERE objectproperty(s.object_id,'IsUserTable') = 1

AND s.database_id = @dbid

AND user_updates > (user_seeks + user_scans + user_lookups)

ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC