Technical Article

Clear all user objects

,

This procedure will drop (with care) all user-defined objects: constraints, procedures, functions, triggers, views and tables. Used for re-creating database schema without drop and create database.

create procedure usp_cleardb
as
begin
    declare @oname varchar(255)
    declare @tname varchar(255)
    declare @sql varchar(1024)
    declare @counter int
    
    declare c_f cursor
        forward_only read_only
        for
        select a.name, b.name from sysobjects a, sysobjects b, sysforeignkeys c
        where a.xtype = 'F' and c.constid = a.id and b.id = c.fkeyid
    
    open c_f
    select @counter = 0
    fetch next from c_f into @oname, @tname
    while @@fetch_status = 0
    begin
        select @sql = 'alter table ' + @tname + ' drop constraint ' + @oname
        exec (@sql)
        select @counter = @counter + 1
        fetch next from c_f into @oname, @tname
    end
    close c_f
    deallocate c_f
    print convert(varchar(5), @counter) + ' constraint(s) deleted'
    
    declare c_p cursor
        forward_only read_only
        for
        select a.name from sysobjects a
        where a.xtype = 'P' and a.name not like 'dt_%' and a.name <> 'usp_cleardb'
    open c_p
    select @counter = 0
    fetch next from c_p into @oname
    while @@fetch_status = 0
    begin
        select @sql = 'drop procedure ' + @oname
        exec (@sql)
        select @counter = @counter + 1
        fetch next from c_p into @oname
    end
    close c_p
    deallocate c_p
    print convert(varchar(5), @counter) + ' procedure(s) deleted'
    

    declare c_fc cursor
        forward_only read_only
        for
        select a.name from sysobjects a
        where a.xtype in ('TF', 'IF', 'FN')
    open c_fc
    select @counter = 0
    fetch next from c_fc into @oname
    while @@fetch_status = 0
    begin
        select @sql = 'drop function ' + @oname
        exec (@sql)
        select @counter = @counter + 1
        fetch next from c_fc into @oname
    end
    close c_fc
    deallocate c_fc
    print convert(varchar(5), @counter) + ' function(s) deleted'
    
    declare c_tr cursor
        forward_only read_only
        for
        select a.name from sysobjects a
        where a.xtype = 'TR' and a.name like 't__%'
    open c_tr
    select @counter = 0
    fetch next from c_tr into @oname
    while @@fetch_status = 0
    begin
        select @sql = 'drop trigger ' + @oname
        exec (@sql)
        select @counter = @counter + 1
        fetch next from c_tr into @oname
    end
    close c_tr
    deallocate c_tr
    print convert(varchar(5), @counter) + ' trigger(s) deleted'

    declare c_v cursor
        forward_only read_only
        for
        select a.name, b.name from sysobjects a, sysusers b
        where a.xtype = 'V' and a.name not like 'sys%' and b.uid = a.uid
    open c_v
    select @counter = 0
    fetch next from c_v into @tname, @oname
    while @@fetch_status = 0
    begin
        select @sql = 'drop view ' + @oname + '.' + @tname
        exec (@sql)
        select @counter = @counter + 1
        fetch next from c_v into @tname, @oname
    end
    close c_v
    deallocate c_v
    print convert(varchar(5), @counter) + ' view(s) deleted'
    
    declare c_u cursor
        forward_only read_only
        for
        select a.name, b.name from sysobjects a, sysusers b
        where a.xtype = 'U' and a.name <> 'dtproperties' and b.uid = a.uid
    open c_u
    select @counter = 0
    fetch next from c_u into @tname, @oname
    while @@fetch_status = 0
    begin
        select @sql = 'drop table ' + @oname + '.' + @tname
        exec (@sql)
        select @counter = @counter + 1
        fetch next from c_u into @tname, @oname
    end
    close c_u
    deallocate c_u
    print convert(varchar(5), @counter) + ' table(s) deleted'
end

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating