As I mentioned yesterday, I'm doing some SQL performance tuning at the moment, and I've ended up looking at a series of indexes and trying to work out which are being used, and which aren't.  A quick google led me to this blog post which gave me a script (which I've since adapted) based on sys.dm_db_index_usage_stats

SELECT 

DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, 

si.name AS IndexName, 

sis.user_seeks, 

sis.user_scans, 

sis.user_lookups, 

sis.user_updates

FROM sys.dm_db_index_usage_stats sis

INNER JOIN sys.indexes si 

ON sis.OBJECT_ID = si.OBJECT_ID 

AND sis.Index_ID = si.Index_ID

WHERE sis.Database_ID = DB_ID('<Database Name>')

which shows me all the indexes that are present for my database.

 

Further restrictions can be made as follows:

 

WHERE sis.Database_ID = DB_ID('<Database Name>')

AND sis.OBJECT_ID = OBJECT_ID('<table name>')

which shows me all the indexes for my database and my specified table

 

WHERE sis.Database_ID = DB_ID('<Database Name>')

AND sis.OBJECT_ID = OBJECT_ID('<table name>')

AND si.name = '<index name>'

which shows me all the indexes for my database, my specified table and my specified index

 

WHERE sis.Database_ID = DB_ID('<Database Name>')

AND sis.user_seeks = 0

AND sis.user_scans = 0

AND sis.user_lookups = 0

which shows me all the indexes in the database which aren't being used - Note: the values held by sys.dm_db_index_usage_stats get reset after a restart of the SQL service