Find all unused indexes in a database

While doing an index analysis activity i had to find the indexes are hardly being used in our production DB’s. I wrote this script to acheive the same

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

SELECT case when os.is_published = 1 then 'Replicated' else 'Not Replicated' end as ReplicatedorNot, OBJECT_NAME(idx.object_id) AS TableName , idx.name as Index_name, istat.user_lookups, istat.user_scans, istat.user_seeks, istat.user_updates FROM sys.dm_db_index_usage_stats AS istat JOIN sys.indexes AS idx ON idx.index_id = istat.index_id join sys.objects os on idx.object_id = os.object_id AND idx.object_id = istat.object_id WHERE istat.database_id = DB_ID('databasename') -- type the name of the Db and object_name(idx.object_id) not like 'Ms%' and object_name(idx.object_id) not like 'sys%' and object_name(idx.object_id) not like 'id_%' and object_name(idx.object_id) not like 'conflict%' and object_name(idx.object_id) not like 'Any other tables that you like to avoid%' AND idx.is_unique_constraint = 0 -- no unique indexes AND idx.is_primary_key = 0 AND idx.is_disabled = 0 AND idx.type > 1 -- don't consider heaps/clustered index AND ( ( istat.user_seeks + istat.user_scans +istat.user_lookups )  <  istat.user_updates or (istat.user_seeks = 0 AND istat.user_scans = 0 AND istat.user_lookups = 0)) order by (istat.user_seeks + istat.user_scans + istat.user_lookups)