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,
@dbnamesysname,
            @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