Oracle & SQL Server: Disable/Enable all Triggers in a Database

Let's keep this post simple and straight forward  ;-)

/* Oracle */

-- Disable All Triggers

BEGIN FOR REC IN (select table_name from user_triggers) LOOP execute immediate 'alter table '|| REC.table_name ||' disable all triggers'; END LOOP; END;

-- Enable All Triggers

BEGIN FOR REC IN (select table_name from user_triggers) LOOP execute immediate 'alter table '|| REC.table_name ||' enable all triggers'; END LOOP; END;

/* SQL Server 2005 */

-- Disable All Triggers
DISABLE TRIGGER ALL ON DATABASE

-- Enable All Triggers

ENABLE TRIGGER ALL ON DATABASE

/* SQL Server 2000 */

-- Disable All Triggers
EXEC sp_MSforeachtable @command1 = 'ALTER TABLE ? DISABLE TRIGGER ALL'

-- Enable All Triggers
EXEC sp_MSforeachtable @command1 = 'ALTER TABLE ? ENABLE TRIGGER ALL'


-- Enjoy!

SQL Server: Execute same operation on all Tables in a Database, in a Go.

I recently found a Gem, i. e. an undocumented Stored Procedure sp_MSforEachTable which you can find in the Master database of your SQL Server 2000 and SQL Server 2005 (you may also find it in SQL Server 2008, but I personally haven't verified it yet). You can execute/use this Stored Procedure in any of your databases to perform the same operation on each and every Table in a database, in one Go.

For example, you may want to temporarily disable all the triggers in your Database. There's a simple way to do that in SQL Server 2005, i. e. by executing command DISABLE TRIGGER ALL ON DATABASE. But, this command doesn't work on SQL Server 2000. So, you may utilize this Stored Procedure to achieve just that in SQL Server 2000. 

-- Disable all triggers in a Database 
EXEC sp_MSforeachtable @command1 = 'ALTER TABLE ? DISABLE TRIGGER ALL'

-- Enable all triggers in a Database
EXEC sp_MSforeachtable @command1 = 'ALTER TABLE ? ENABLE TRIGGER ALL'

Note: During execution of this command, the Question Mark (?) will be replaced by Tables' names one after another to execute the same command on each and every Table in the Database.

There is also a way you can filter the set of Tables you want to target. Given below is the complete definition of sp_MSforEachTable Stored Procedure. You can explore and do more.

create proc sys.sp_MSforeachtable 
 @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 hCForEachTable cursor global for select ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' from dbo.sysobjects o join sys.all_objects syso on o.id =
 syso.object_id ' 
         + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 ' 
         + @whereand) 
 declare @retval int 
 select @retval = @@error 
 if (@retval = 0) 
  exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0 
 
 if (@retval = 0 and @postcommand is not null) 
  exec(@postcommand) 
 
 return @retval