Drop Indexes

/*
Drop Indexes By Ramunas Balukonis, 2004.03.17
*/

CREATE proc dbo.pr_DropIndexes  (@objname nvarchar(776))		-- the table to check for indexes)
as

declare	@db_name sysname
declare @sqlExe nvarchar(4000)
set @db_name = db_name()

	-- PRELIM
	set nocount on

	declare @indname sysname


	select name as indname
		, convert(bit, (status & 2048)) as Pk
		, convert(bit, (status & 16)) as Clust 
	into #spindtab
	from sysindexes
	where id = object_id (@objname) and indid > 0 and indid < 255 and (status & 64)=0 order by indid

	declare crs_idx cursor fast_forward read_only for 
	select indname from #spindtab where pk = 0 order by Clust

	open crs_idx
	fetch next from crs_idx into @indname
	while @@fetch_status = 0
	begin
		set @sqlExe = 'drop index ' + @objname + '.' + @indname
		/* drop indexes */
		--print (@sqlExe)
		exec (@sqlExe)
		fetch next from crs_idx into @indname
	end
	close crs_idx
	deallocate crs_idx

	/* drop constraint */
	select @indname = indname from #spindtab where pk = 1
	if @@rowcount > 0
	begin
		set @sqlExe = 'alter table ' + @objname + ' drop constraint ' + @indname
		--print (@sqlExe)
		exec (@sqlExe)
	end

	return 0
GO