create procedure cleardb(@username varchar(32) in)
as
begin
declare @strdrop varchar(512)
select @strdrop=''
set @strdrop='setuser ''' + @username + ''''
print @strdrop
/* 1begin drop all constraints */
declare @tname varchar(128), @tableid int, @cid int
declare @cname varchar(128)
set @strdrop=''
declare c_constraints cursor for select a.id, c.tableid, a.name from sysobjects a, sysusers b, sysconstraints c where a.type='RI' and a.uid=b.uid and b.name=@username and c.constrid=a.id
open c_constraints
fetch next from c_constraints into @cid, @tableid, @cname
while (@@sqlstatus=0)
begin
select @tname=name from sysobjects where id=@tableid
set @strdrop='alter table ' + @username + '.' + @tname + ' drop constraint ' + @cname
print @strdrop
fetch next from c_constraints into @cid, @tableid, @cname
end
close c_constraints
deallocate cursor c_constraints
/*2 drop tables */
set @tname=''
set @strdrop=''
declare c_tables cursor for select a.name from sysobjects a, sysusers b where a.type='U' and a.uid=b.uid and b.name=@username
open c_tables
fetch next from c_tables into @tname
while (@@sqlstatus = 0)
begin
set @strdrop = 'drop table ' + @username + '.' + @tname
print @strdrop
fetch next from c_tables into @tname
end
close c_tables
deallocate cursor c_tables
/*3 drop procedures*/
declare @procname varchar(128)
declare c_procs cursor for select a.name from sysobjects a, sysusers b where a.type='P' and a.uid=b.uid and b.name=@username
open c_procs
fetch next from c_procs into @procname
while (@@sqlstatus=0)
begin
set @strdrop = 'drop procedure ' + @username + '.' + @procname
print @strdrop
fetch next from c_procs into @procname
end
close c_procs
deallocate cursor c_procs
set @strdrop='setuser '
print @strdrop
end
Code