Technical Article

sp_MSforeachtrigger-Perform action on each trigger

,

This stored procedure loops through each trigger and performs an action on that trigger , like disabling or printing it. Please install this stored procedure in the MASTER database. For full usage instructions and advanced parameters please see the article about it at

http://www.sqlservercentral.com/columnists/bknight/sp_msforeachworker.asp.



Shortened version of the usage (there are a lot more parameters that can be seen on the article.):
   sp_MSforeachtrigger @COMMAND1 = "Print '?'"

USE MASTER
GO

if exists (select name from sysobjects 
where name = 'sp_MSforeachtrigger' AND type = 'P')
drop procedure sp_MSforeachtrigger
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create proc sp_MSforeachtrigger
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null,
   @command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null,
@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
/* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set *//* @precommand and @postcommand may be used to force a single result set via a temp table. */
/* Preprocessor won't replace within quotes so have to use str(). */declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))

if (@precommand is not null)
exec(@precommand)

/* Create the select */   exec(N'declare hCForEach cursor global for select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(id), N'']'', N'']]'') + '']'' from dbo.sysobjects o '
         + N' where OBJECTPROPERTY(o.id, N''IsTrigger'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '
         + @whereand)
declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

if (@retval = 0 and @postcommand is not null)
exec(@postcommand)

return @retval

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

Share

Share

Rate