SQL Server disables, enables foreign KEY constraints

---Enable or disable all foreign KEY constraints on the specified table  ALTER TABLE Pub_stru  NOCHECK constraint all;  ALTER TABLE Pub_stru  CHECK constraint all;    ---Build enables or disables the specified table FOREIGN KEY constraint for 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= ' table 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= ' table name ';  -generated SQL is as follows 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;    --View constraint status (query dictionary table Sys.foreign_keys, which starts appearing in sqlserver2005 and above): select Name, is_disabled from Sys.foreign_keys order by Name;   --Where: Name  : FOREIGN KEY constraint name   is_disabled: Disabled

　　

Example:

--Delete foreign key ALTER TABLE AdItem DROP constraint adorder_aditem_fk1--add foreign key ALTER TABLE AdItem add constraint adorder_aditem_fk1 for Eign Key (Ai_norderno) references Adorder (Ao_norderno)--a foreign key to a single table ALTER TABLE Student nocheck constraint fk__student__ SCHOOLN__4222D4EF  ALTER TABLE Student CHECK constraint fk__student__schooln__4222d4ef  -all foreign keys of a single table ALTER TABLE Student nocheck constraint  all ALTER TABLE Student CHECK constraint  all – all tables of a database exec sp_msforeachtable @ command1= ' ALTER TABLE?  NOCHECK constraint all; EXEC sp_msforeachtable @command1 = ' ALTER TABLE?  CHECK constraint all;

Reference:
enable/disable Constraint in SQL Server
Sp_msforeachtable How to use

SQL Server disables, enables foreign KEY constraints