Sybase ASE出现表间循环依赖不能删除的问题 无忧教程网整理

【赛迪网－IT技术报道】写了一个简单的脚本来清除一个用户所拥有的表以及存储过程。感兴趣的可以试一下。我是初次写ASE下边的存储过程，肯定有更好的解决方法。本想直接在存储过程里drop东西，可惜失败了。这个存储过程只是生成了清除表的sql脚本。拷贝再执行即可。

具体示例如下：

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

（责任编辑：卢兆林）