Using AdventureWorks2008, we can list the indexes belonging to the TransactionHistory table ...
select * from sys.indexes where object_id = object_id('production.transactionhistory')
Here we find out how they have been used ...
select i.name, s.* from sys.dm_db_index_usage_stats s inner join sys.indexes i on s.object_id = i.object_id and s.index_id = i.index_id where database_id = DB_ID('adventureworks2008') and s.object_id = object_id('production.transactionhistory')
The view returns counts of seeks, scans and lookup operations as well as timestamps for the latest ones ...
This view is utilised by my Unused index and Index Sizes script as well as this simple example showing index usage in the current database.
Louis Davidson has an excellent article here >
The MSDN reference is here >