神马文学网如何删除非系统的表，存储过程，自定义函数,触发器
来源：百度文库 编辑：神马文学网 时间：2024/04/29 03:43:55
/******执行此操作时请慎重,确认无误后把事务去掉既可******/
BEGIN TRAN --事务 开始
select * from sysobjects where (type='U' or type='P' or type='FN' or type='TR') and category<>2
declare @StrName nvarchar(200)
--删除 函数
declare cursorFN cursor for
select name from sysobjects where type='FN'
open cursorFN
while 1=1
begin
fetch next from cursorFN into @StrName
if @@fetch_status<>0 break
exec('DROP FUNCTION '+@StrName)
end
close cursorFN
deallocate cursorFN
--删除 存储过程
declare cursorP2 cursor for
select name from sysobjects where type='P' and category<>2
open cursorP2
while 1=1
begin
fetch next from cursorP2 into @StrName
if @@fetch_status<>0 break
exec('DROP PROCEDURE '+@StrName)
end
close cursorP2
deallocate cursorP2
--删除 表 (删时自动删除触发器)
--1删无约束无外键的表
declare cursorU1 cursor for
select a.name from sysobjects a where not exists(select 1 from sysobjects b where a.id=b.parent_obj) and a.type='U'
open cursorU1
while 1=1
begin
fetch next from cursorU1 into @StrName
if @@fetch_status<>0 break
exec('DROP TABLE '+@StrName)
end
close cursorU1
deallocate cursorU1
--2删有约束或有外键的表
while exists(select 1 from sysconstraints)
begin
declare cursorU2 cursor for
select c.name from sysobjects c join (select distinct id from sysconstraints a
where not exists(select 1 from sysforeignkeys b where a.id=b.rkeyid)) d on c.id=d.id where c.type='U'
open cursorU2
while 1=1
begin
fetch next from cursorU2 into @StrName
if @@fetch_status<>0 break
exec('DROP TABLE '+@StrName)
end
close cursorU2
deallocate cursorU2
end
select * from sysobjects where (type='U' or type='P' or type='FN' or type='TR') and category<>2
ROLLBACK TRANSACTION --事务 回滚