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‘