Drop All Triggers
Drop All Triggers
This script produces a script that drops and creates all triggers on a certain database. To use it create the procedure in the master db and save the result as *.sql.
Author: Marino De Veirman
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