gistfile1.txt

declare @i float = 0.5

select *, (rn_user_scans + rn_user_seeks + rn_user_updates)/3 t
from (
	SELECT  
		i.name,
		sts.last_user_seek,
		sts.last_user_scan,
		percent_rank() over (order by sts.user_scans) rn_user_scans,
		sts.user_scans,
		percent_rank() over (order by sts.user_seeks desc) rn_user_seeks,
		sts.user_seeks,
		percent_rank() over (order by sts.user_updates) rn_user_updates,
		sts.user_updates,
		'DROP INDEX [' + i.name + '] ON [' + OBJECT_SCHEMA_NAME(o.object_id) + '].[' + OBJECT_NAME(o.object_id) + ']' as script
	from sys.indexes i 
	join sys.objects o on o.object_id = i.object_id
	left join sys.dm_db_index_usage_stats sts 
		on sts.index_id = i.index_id 
		and sts.object_id = i.object_id
	where 1=1
	and i.is_primary_key = 0
	and i.is_unique = 0
	and i.name is not null
	and o.type = 'u'
	--and ((last_user_seek < dateadd(week, -1, getdate()) or last_user_seek is null))
) t
where 1=1
and rn_user_scans > @i
and rn_user_seeks > @i
and rn_user_updates > @i
order by t desc