Drop table indexes, constraints and statistics

--
-- 04/11/2001, Arek
-- JFF Software
create proc utl_drop_all_indexes
	@objname nvarchar(776)		-- the table
as
	-- PRELIM
	set nocount on
	declare @objid int,			-- the object id of the table
			@indid smallint,	-- the index id of an index
			@indname sysname,
			@status int,
			@dbname	sysname,
            @tablename sysname,
            @statement nvarchar(511)
	-- Check to see that the object names are local to the current database.
	select @dbname = parsename(@objname,3) 
	if @dbname is not null and @dbname <> db_name()
	begin
			raiserror(15250,-1,-1)
			return (1)
	end
	-- Check to see the the table exists and initialize @objid.
	select @objid = object_id(@objname)
	if @objid is NULL
	begin
		select @dbname = db_name()
		raiserror(15009,-1,-1,@objname,@dbname)
		return (1)
	end
    select @tablename = parsename(@objname,1)
	-- OPEN CURSOR OVER INDEXES
	declare ind_curs insensitive cursor for
		select indid, name, status 
            from sysindexes
			where id = @objid and indid > 0 and indid < 255 order by indid
	open ind_curs
	fetch ind_curs into @indid, @indname, @status
	-- IF NO INDEX, QUIT
	if @@fetch_status < 0
	begin
		deallocate ind_curs
		raiserror(15472,-1,-1) --'Object does not have any indexes.'
		return (0)
	end
    BEGIN TRANSACTION drop_indexes
	-- Now get each index, figure out its type and drop it ;-)
	while @@fetch_status >= 0
	begin
        -- determine type and drop
        -- if you are interested see output from this
        -- select name, number from master..spt_values where type = 'I'
        if (@status & 2048) <> 0 begin
            -- primary key
            select @statement = 'alter table ' + @tablename + ' drop constraint ' + @indname
        end
        else if (@status & 64) <> 0 or (@status & 16777216) <> 0 begin
            -- statistics
            select @statement = 'drop statistics  ' + @tablename + '.' + @indname
        end
        else begin
            -- other index
            select @statement = 'drop index  ' + @tablename + '.' + @indname
        end
        exec sp_executesql @statement
		-- Next index
		fetch ind_curs into @indid, @indname, @status
	end
    COMMIT TRANSACTION drop_indexes
	deallocate ind_curs
    print 'All indexes droped'
return (0) -- all done

GO