Drop All Non-Primary-Key Indexes

A script that generates DROP INDEX statements for every non-primary-key index in the database.

Published Sep 19, 2006

Preparing a table for a bulk operation and you want to drop every secondary index first? This query generates the DROP INDEX statement for every non-primary-key, non-heap index on every user table - copy what you need and run it. (Remember to recreate them afterwards.)

select
	'DROP INDEX [' + schema_name(o.schema_id) + '].[' + o.name + '].[' + i.name + ']'
from
	sys.objects as o
inner join sys.indexes as i
	on i.object_id = o.object_id
where
	o.is_ms_shipped = 0
	and i.is_primary_key = 0 --We do not want to dop primary keys.
	and i.type_desc <> 'HEAP'