編寫人：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'