SQL 中删除某数据库所有trigger及sp
编写人： CC 阿爸
2014-6-14
在日常 SQL 数据库的操作中，如何快速的删除所有 trigger 及 sp 呢
以下有三种方式可快速处理。
-- 第一种
-- 事务的处理方法
Begin Transaction
Begin try
declare @SQL varchar ( max )
set @SQL = ''
select @SQL = @SQL + name + ',' from sysobjects where xtype = 'TR' and name <> 'DropDatabase'
If ISNULL ( @SQL , '' )!= ''
Begin
set @SQL = 'Drop Trigger ' +LEFT( @SQL , len ( @SQL )- 1 )
select @SQL as aa
--exec(@SQL)
end
commit Transaction
End Try
Begin Catch
rollback tran
End Catch
-- 第二种方法
-- 采用光标的方式
--DECLARE cursorname cursor for select 'drop PROCEDURE '+name from sys.objects where name like 'xx%' and xtype = 'P' -- 删除对应的存储过程
DECLARE cursorname cursor for select 'drop Trigger' + name from sys . objects where name like '%' and type = 'TR' -- 删除对应的触发器
open cursorname
declare @curname sysname
fetch next from cursorname into @curname
while ( @@fetch_status = 0 )
begin
--exec(@curname)
select @curname as aa
fetch next from cursorname into @curname
end
close cursorname
deallocate cursorname
-- 第三种方法
-- 简易办法，查询出来后，再在数据库中执行
select 'drop Trigger ' + name from sys . objects where name like '%' and type = 'TR'
select 'drop PROCEDURE ' + name from sys . objects where name like '%' and type = 'P'
