Enable and Disable all the Triggers on all the tables in SQL Server 2005


Create PROCEDURE TRIGGEREnable (@action varchar(50))
AS
/**
* @Author: Tsepo D. Mohlapo
* @Date: 2010 April 14
* @Description: Disable / Enable All Triggers on all tables
*
**/
IF (@action = ‘Enable’ OR @action = ‘Disable’)
 BEGIN
DECLARE @Tables TABLE(
  primary_key INT IDENTITY(1,1) NOT NULL,
  schema_name NVARCHAR(100),
  table_name NVARCHAR(100)
  
 )

 INSERT INTO @Tables
 SELECT DISTINCT
   S.name as [Schema name],
   object_name(T.object_id) AS [Table name]
  FROM
   sys.tables T
   LEFT JOIN sys.schemas S ON (S.schema_id = T.schema_id)
   
       
  WHERE
   T.object_id is not null

 DECLARE @l_count INT
 DECLARE @row_count INT

 SET @l_count = ISNULL((SELECT COUNT(*) FROM @Tables),0)
 SET @row_count = 1

 DECLARE @schema_name VARCHAR(100)
 DECLARE @table_name VARCHAR(100)
 DECLARE @sql NVARCHAR(1000)

 WHILE @l_count > 0 AND @row_count <= @l_count
 BEGIN
   
  
   SELECT
    @schema_name = schema_name,
    @table_name = table_name
   FROM
    @Tables
   WHERE
    primary_key = @row_count

  
 
 
 
 SET @sql = ‘
ALTER TABLE  [‘+ @schema_name+ ‘].[‘ + @table_name + ‘]  ‘ +  @action + ‘ TRIGGER ALL


 
 Print @sql –Can comment out

 EXEC sp_executesql @sql

SET @row_count =  @row_count + 1

 END
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: