Sybase ASE There is a problem in which cyclic dependencies between tables cannot be deleted

Source: Internet
Author: User
Tags sybase

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.