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

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

一个脚本来管理它们:这个脚本将截断和删除命令与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

找到约束条件

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('TABLE_NAME')

执行由此SQL生成的SQL

SELECT 
    'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(parent_object_id) +
    '.[' + OBJECT_NAME(parent_object_id) + 
    '] DROP CONSTRAINT ' + name
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('TABLE_NAME')

西夫韦。

注意:增加了删除约束的解决方案，这样可以删除或修改表而没有任何约束错误。

右键单击表设计，进入“关系”，在左侧窗格中选择外键，在右侧窗格中，将强制外键约束设置为“Yes”(启用外键约束)或“No”(禁用外键约束)。

如果你感兴趣，我有一个更有用的版本。我从这里提取了一些代码，一个网站的链接不再活跃。我修改了它，以允许存储过程中有一个表数组，并在执行所有语句之前填充删除、截断和添加语句。这使您可以控制决定哪些表需要截断。

/****** Object:  UserDefinedTableType [util].[typ_objects_for_managing]    Script Date: 03/04/2016 16:42:55 ******/
CREATE TYPE [util].[typ_objects_for_managing] AS TABLE(
    [schema] [sysname] NOT NULL,
    [object] [sysname] NOT NULL
)
GO

create procedure [util].[truncate_table_with_constraints]
@objects_for_managing util.typ_objects_for_managing readonly

--@schema sysname
--,@table sysname

as 
--select
--    @table = 'TABLE',
--    @schema = 'SCHEMA'

declare @exec_table as table (ordinal int identity (1,1), statement nvarchar(4000), primary key (ordinal));

--print '/*Drop Foreign Key Statements for ['+@schema+'].['+@table+']*/'

insert into @exec_table (statement)
select
          'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+ o.name+'] DROP CONSTRAINT ['+fk.name+']'
from sys.foreign_keys fk
inner join sys.objects o
          on fk.parent_object_id = o.object_id
where 
exists ( 
select * from @objects_for_managing chk 
where 
chk.[schema] = SCHEMA_NAME(o.schema_id)  
and 
chk.[object] = o.name
) 
;
          --o.name = @table and
          --SCHEMA_NAME(o.schema_id)  = @schema

insert into @exec_table (statement) 
select
'TRUNCATE TABLE ' + src.[schema] + '.' + src.[object] 
from @objects_for_managing src
; 

--print '/*Create Foreign Key Statements for ['+@schema+'].['+@table+']*/'
insert into @exec_table (statement)
select 'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+o.name+'] ADD CONSTRAINT ['+fk.name+'] FOREIGN KEY (['+c.name+']) 
REFERENCES ['+SCHEMA_NAME(refob.schema_id)+'].['+refob.name+'](['+refcol.name+'])'
from sys.foreign_key_columns fkc
inner join sys.foreign_keys fk
          on fkc.constraint_object_id = fk.object_id
inner join sys.objects o
          on fk.parent_object_id = o.object_id
inner join sys.columns c
          on      fkc.parent_column_id = c.column_id and
                   o.object_id = c.object_id
inner join sys.objects refob
          on fkc.referenced_object_id = refob.object_id
inner join sys.columns refcol
          on fkc.referenced_column_id = refcol.column_id and
                   fkc.referenced_object_id = refcol.object_id
where 
exists ( 
select * from @objects_for_managing chk 
where 
chk.[schema] = SCHEMA_NAME(o.schema_id)  
and 
chk.[object] = o.name
) 
;

          --o.name = @table and
          --SCHEMA_NAME(o.schema_id)  = @schema



declare @looper int , @total_records int, @sql_exec nvarchar(4000)

select @looper = 1, @total_records = count(*) from @exec_table; 

while @looper <= @total_records 
begin

select @sql_exec = (select statement from @exec_table where ordinal =@looper)
exec sp_executesql @sql_exec 
print @sql_exec 
set @looper = @looper + 1
end

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

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