USE DatabaseName
GO
SELECT Top 100 object_name(a.object_id) AS table_name,
COALESCE(name, 'table with no clustered index') AS index_name,
type_desc AS index_type,
user_seeks,
user_scans,
user_lookups,
user_updates
,a.object_id
FROM sys.dm_db_index_usage_stats a INNER JOIN
sys.indexes b ON a.index_id = b.index_id AND a.object_id = b.object_id
WHERE database_id = DB_ID('DatabaseName ') AND a.object_id > 1000
ORDER BY user_seeks desc, user_scans desc
This results help you to analyse the index usage on a database and do the necessary action on less used or unused indexes...