一个脚本来管理它们:这个脚本将截断和删除命令与sp_MSforeachtable结合在一起，这样您就可以避免删除和重新创建约束——只需指定需要删除而不是截断的表，为了便于度量，我还包含了一个额外的模式过滤器(在2008r2中测试)。

declare @schema nvarchar(max) = 'and Schema_Id=Schema_id(''Value'')'
declare @deletiontables nvarchar(max) = '(''TableA'',''TableB'')'
declare @truncateclause nvarchar(max) = @schema + ' and o.Name not in ' +  + @deletiontables;
declare @deleteclause nvarchar(max) = @schema + ' and o.Name in ' + @deletiontables;        

exec sp_MSforeachtable 'alter table ? nocheck constraint all', @whereand=@schema
exec sp_MSforeachtable 'truncate table ?', @whereand=@truncateclause
exec sp_MSforeachtable 'delete from ?', @whereand=@deleteclause
exec sp_MSforeachtable 'alter table ? with check check constraint all', @whereand=@schema

你可以使用以下命令轻松切换CONSTRAINT: 修改表TableName不检查约束所有

完成交易后，不要忘记使用以下方法再次开启: 修改表表名检查约束全部

SQL-92标准允许将约束声明为DEFERRABLE，以便在事务范围内(隐式或显式)延迟它。遗憾的是，SQL Server仍然缺少这个SQL-92功能。

对我来说，将约束更改为NOCHECK类似于动态更改数据库结构——删除约束当然是这样——这是需要避免的(例如，用户需要增加特权)。

要禁用约束，您可以使用NOCHECK更改表

ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [ALL|CONSTRAINT_NAME]

要使您必须使用双重检查:

ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]

启用时请注意双重CHECK CHECK。 ALL表示表中的所有约束。

一旦完成，如果您需要检查状态，使用这个脚本列出约束状态。会很有帮助:

    SELECT (CASE 
        WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
        ELSE 'DISABLED'
        END) AS STATUS,
        OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
        OBJECT_NAME(FKEYID) AS TABLE_NAME,
        COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
        OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
        COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
   FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO 

SET NOCOUNT ON

DECLARE @table TABLE(
   RowId INT PRIMARY KEY IDENTITY(1, 1),
   ForeignKeyConstraintName NVARCHAR(200),
   ForeignKeyConstraintTableSchema NVARCHAR(200),
   ForeignKeyConstraintTableName NVARCHAR(200),
   ForeignKeyConstraintColumnName NVARCHAR(200),
   PrimaryKeyConstraintName NVARCHAR(200),
   PrimaryKeyConstraintTableSchema NVARCHAR(200),
   PrimaryKeyConstraintTableName NVARCHAR(200),
   PrimaryKeyConstraintColumnName NVARCHAR(200),
   UpdateRule NVARCHAR(100),
   DeleteRule NVARCHAR(100)   
)

INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT 
   U.CONSTRAINT_NAME, 
   U.TABLE_SCHEMA, 
   U.TABLE_NAME, 
   U.COLUMN_NAME
FROM 
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
         ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
   C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @table SET
   T.PrimaryKeyConstraintName = R.UNIQUE_CONSTRAINT_NAME,
   T.UpdateRule = R.UPDATE_RULE,
   T.DeleteRule = R.DELETE_RULE
FROM 
   @table T
      INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
         ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintTableSchema  = TABLE_SCHEMA,
   PrimaryKeyConstraintTableName  = TABLE_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
      ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--SELECT * FROM @table

SELECT '
BEGIN TRANSACTION
BEGIN TRY'

--DROP CONSTRAINT:
SELECT
   '
 ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] 
 DROP CONSTRAINT ' + ForeignKeyConstraintName + '
   '
FROM
   @table

SELECT '
END TRY

BEGIN CATCH
   ROLLBACK TRANSACTION
   RAISERROR(''Operation failed.'', 16, 1)
END CATCH

IF(@@TRANCOUNT != 0)
BEGIN
   COMMIT TRANSACTION
   RAISERROR(''Operation completed successfully.'', 10, 1)
END
'

--ADD CONSTRAINT:
SELECT '
BEGIN TRANSACTION
BEGIN TRY'

SELECT
   '
   ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] 
   ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ') ON UPDATE ' + UpdateRule + ' ON DELETE ' + DeleteRule + '
   '
FROM
   @table

SELECT '
END TRY

BEGIN CATCH
   ROLLBACK TRANSACTION
   RAISERROR(''Operation failed.'', 16, 1)
END CATCH

IF(@@TRANCOUNT != 0)
BEGIN
   COMMIT TRANSACTION
   RAISERROR(''Operation completed successfully.'', 10, 1)
END'

GO

标记为“905”的答案看起来不错，但不能工作。

下面的方法对我很有效。不能禁用任何主键、唯一键或默认约束。事实上，如果“sp_helpconstraint”在status_enabled中显示“n/a”-意味着它不能被启用/禁用。

生成脚本为DISABLE

select 'ALTER TABLE ' + object_name(id) + ' NOCHECK CONSTRAINT [' + object_name(constid) + ']'
from sys.sysconstraints 
where status & 0x4813 = 0x813 order by object_name(id)

——生成脚本以启用

select 'ALTER TABLE ' + object_name(id) + ' CHECK CONSTRAINT [' + object_name(constid) + ']'
from sys.sysconstraints 
where status & 0x4813 = 0x813 order by object_name(id)