转帖自：

怎么一次性删除业务数据库所有表的触发器?
---------回复--------------
SELECT 'drop trigger '+NAME+ ' go ' FROM SYSOBJECTS
WHERE TYPE= 'TR '
批量执行结果集

---------回复--------------
楼上的只是查询,修改下.

declare @sql varchar(8000)
set @sql= ' '
SELECT @sql=@sql+ 'drop trigger '+NAME+char(13) FROM SYSOBJECTS
WHERE TYPE= 'TR '
exec(@sql)
---------回复--------------
declare @sql nvarchar(4000)
set @sql= ' '
select @sql=@sql+ 'drop trigger '+NAME+ ' ' from dbo.sysobjects where OBJECTPROPERTY(id, N 'IsTrigger ') = 1
print @sql
execute sp_executesql @sql
---------回复--------------
if exists (select * from sysobjects where id = object_id(N '[dbo].[sp_ScriptTriggers] ') and OBJECTPROPERTY(id, N 'IsProcedure ') = 1)
drop procedure [dbo]. [sp_ScriptTriggers]
GO

use master
go

create procedure sp_ScriptTriggers
as
/* ************************************************************************************************************* */
/* AUTHOR : De Veirman Marino CREATION DATE : 06/07/2000 */
/* COMPANY : Spector Photo Group N.V. */
/* DESCRIPTION : To use this procedure create it in the master database and run it from any tool that can return */
/* a print statement. Save the output result as *.sql */
/* ************************************************************************************************************* */

set nocount on

declare @name sysname
declare @text varchar(4096)
declare @SQL varchar(255)

declare cur_triggers insensitive cursor for

select name from sysobjects where OBJECTPROPERTY(id, 'ISTRIGGER ') = 1

open cur_triggers

fetch next from cur_triggers into @name
while (@@fetch_status <> -1) begin

print '/* DROP TRIGGER ' + @name + ' ----------------------------------------- */ '
print '/* SCRIPTED ' + Convert(varchar(50),GETDATE()) + ' ---------------- */ '

select @text = 'if exists (select * from sysobjects where id = object_id(N ' ' ' + @name + ' ' ') and OBJECTPROPERTY(id, N ' 'IsTrigger ' ') = 1) '
print @text
select @text = 'drop trigger ' + @name
print @text
print 'GO '

print '/* CREATE TRIGGER ' + @name + '---------------------------------------- */ '
print '/* SCRIPTED ' + Convert(varchar(50),GETDATE()) + ' ----- --------- */ '
print ' '

select @text = text from syscomments where id = OBJECT_ID(@name)
print @text
print 'GO '

fetch next from cur_triggers into @name
end
close cur_triggers
deallocate cur_triggers
set nocount off