摘要：
更改表PUB_ STRU CHECK约束所有；sysobjectsb，其中a.xtype='f'和a.parent_Obj=b.id和b.name=“表名”；sysobjectsb，其中a.xtype='f'和a.parent_Obj=b.id和b.name=“表名”；
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | ---启用or禁用指定表所有外键约束 alter table PUB_STRU NOCHECK constraint all; alter table PUB_STRU CHECK constraint all; ---生成启用or禁用指定表外键约束的sql select 'ALTER TABLE ' + b.name + ' NOCHECK CONSTRAINT ' + a.name +';' from sysobjects a ,sysobjects b where a.xtype ='f' and a.parent_obj = b.id and b.name='表名'; select 'ALTER TABLE ' + b.name + ' CHECK CONSTRAINT ' + a.name +';' from sysobjects a ,sysobjects b where a.xtype ='f' and a.parent_obj = b.id and b.name='表名'; --生成的sql如下ALTER TABLE PUB_STRU NOCHECK CONSTRAINT PUBSTRU_FK1;ALTER TABLE PUB_STRU NOCHECK CONSTRAINT PUBSTRU_FK2;ALTER TABLE PUB_STRU CHECK CONSTRAINT PUBSTRU_FK1;ALTER TABLE PUB_STRU CHECK CONSTRAINT PUBSTRU_FK2; --查看约束状态（查询字典表 sys.foreign_keys，该字典表开始出现于sqlserver2005及以上版本）：select name , is_disabled from sys.foreign_keys order by name; --其中：name ： 外键约束名称 is_disabled : 是否已禁用 |
例子：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | --删除外键alter table AdItem drop constraint AdOrder_AdItem_FK1--增加外键alter table AdItemadd constraint AdOrder_AdItem_FK1 foreign key (AI_nOrderNo) references AdOrder(AO_nOrderNo)--单个表的一个外键alter table Student nocheck constraint FK__Student__SchoolN__4222D4EF alter table Student check constraint FK__Student__SchoolN__4222D4EF --单个表的所有外键alter table Student nocheck constraint all alter table Student check constraint all --某个数据库的所有表EXEC sp_MSforeachtable @command1='alter table ? NOCHECK constraint all;EXEC sp_MSforeachtable @command1='alter table ? CHECK constraint all; |
参考：
Enable/Disable Constraint in SQLServer
sp_MSforeachtable使用方法
--启用or禁用指定表所有外键约束 alter table PUB_STRU NOCHECK constraint all; alter table PUB_STRU CHECK constraint all; ---生成启用or禁用指定表外键约束的sql select 'ALTER TABLE ' + b.name + ' NOCHECK CONSTRAINT ' + a.name +';' from sysobjects a ,sysobjects b where a.xtype ='f' and a.parent_obj = b.id and b.name='表名'; select 'ALTER TABLE ' + b.name + ' CHECK CONSTRAINT ' + a.name +';' from sysobjects a ,sysobjects b where a.xtype ='f' and a.parent_obj = b.id and b.name='表名'; --生成的sql如下ALTER TABLE PUB_STRU NOCHECK CONSTRAINT PUBSTRU_FK1;ALTER TABLE PUB_STRU NOCHECK CONSTRAINT PUBSTRU_FK2;ALTER TABLE PUB_STRU CHECK CONSTRAINT PUBSTRU_FK1;ALTER TABLE PUB_STRU CHECK CONSTRAINT PUBSTRU_FK2; --查看约束状态（查询字典表 sys.foreign_keys，该字典表开始出现于sqlserver2005及以上版本）：select name , is_disabled from sys.foreign_keys order by name; --其中：name ： 外键约束名称 is_disabled : 是否已禁用 |
例子：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | --删除外键alter table AdItem drop constraint AdOrder_AdItem_FK1--增加外键alter table AdItemadd constraint AdOrder_AdItem_FK1 foreign key (AI_nOrderNo) references AdOrder(AO_nOrderNo)--单个表的一个外键alter table Student nocheck constraint FK__Student__SchoolN__4222D4EF alter table Student check constraint FK__Student__SchoolN__4222D4EF --单个表的所有外键alter table Student nocheck constraint all alter table Student check constraint all --某个数据库的所有表EXEC sp_MSforeachtable @command1='alter table ? NOCHECK constraint all;EXEC sp_MSforeachtable @command1='alter table ? CHECK constraint all; |
参考：
Enable/Disable Constraint in SQLServer
sp_MSforeachtable使用方法