Wrote a simple script to clear a user's own table and stored procedures. Interested can try. I am the first to write ASE below the stored procedures, there must be a better solution. I wanted to drop something directly in the stored procedure, but it failed. This stored procedure simply generates a SQL script that clears the table. The copy can be executed again.
The specific examples are as follows:
CREATE PROCEDURE Cleardb (@username varchar)
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.ty Pe= ' 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= @usern Ame
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= @userna Me
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