Copy and paste the script onto your SSMS and execute passing the required parameter.
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