This code can be used to identify all indexed that never have been used

This lists all indexes that have not been used since the last start up of sql server 2005 instance. The index have bene updated (caused IO and CPU) but never referenced, used, scaned, seeked in etc. Note, before you delete any index, always run this for a long time. It might be that the code using the index just runs once every 3 month or so! This gives us a very good vire of how the indexes is not used anyway.

select object_name(i.object_id),
i.name,
s.user_updates,
s.user_seeks,
s.user_scans,
s.user_lookups
from sys.indexes i left join sys.dm_db_index_usage_stats s
on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id = 5
where objectproperty(i.object_id, 'IsIndexable') = 1 and
-- index_usage_stats has no reference to this index (not being used)
s.index_id is null or
-- index is being updated, but not used by seeks/scans/lookups
(s.user_updates > 0 and s.user_seeks = 0
and s.user_scans = 0 and s.user_lookups = 0)
order by object_name(i.object_id) asc