暂停/启用所有触发器及外键约束

/*
说明： 暂停/启用所有触发器及外键约束
*/

ALTER  PROCEDURE [dbo].[P_set_trigger] @flag bit AS

SET NOCOUNT ON

DECLARE @TableName sysname, @ConstraintName sysname, @Action nvarchar(7), @SQLString nvarchar(500)

-- TRIGGER

SET @Action = case when @flag = 1 then 'ENABLE' else 'DISABLE' end

DECLARE cTmp CURSOR FOR SELECT name FROM sysobjects WHERE xtype = N'U'

OPEN cTmp
FETCH cTmp INTO @TableName

WHILE (@@FETCH_STATUS = 0)
BEGIN

 SET @SQLString = 'ALTER TABLE ' + @TableName + ' ' + @Action + ' TRIGGER all'

 EXEC sp_executesql @SQLString

 -- Next
 FETCH cTmp INTO @TableName

END

CLOSE cTmp
DEALLOCATE cTmp

if @flag = 1
 PRINT 'Set all Trigger to ENABLE'
else
 PRINT 'Set all Trigger to DISABLE'

-- CONSTRAINT

SET @Action = case when @flag = 1 then 'CHECK' else 'NOCHECK' end

DECLARE cTmp CURSOR FOR SELECT object_name(parent_obj), name FROM sysobjects WHERE xtype = N'F'

OPEN cTmp
FETCH cTmp INTO @TableName, @ConstraintName

WHILE (@@FETCH_STATUS = 0)
BEGIN

 SET @SQLString = 'ALTER TABLE ' + @TableName + ' ' + @Action + ' CONSTRAINT ' + @ConstraintName

 EXEC sp_executesql @SQLString

 -- Next
 FETCH cTmp INTO @TableName, @ConstraintName

END

CLOSE cTmp
DEALLOCATE cTmp

if @flag = 1
 PRINT 'Set all Constraint to ENABLE'
else
 PRINT 'Set all Constraint to DISABLE'

SET NOCOUNT OFF