Technical Article

Disable / Enable All triggers on all tables in sql server 2005

,

Copy and paste the script onto your SSMS and execute passing the required parameter.

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

Rate

3 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (1)

You rated this post out of 5. Change rating