How to correct a SQL Server Syntax Error setting FK contrainsts?

Question

Trying to run this query in SSMS 2012:

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE dbo.mydb;

SET FOREIGN_KEY_CHECKS = 1;

Results in this error on the Foreign Key check

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.

So both SET commands are failing for this reason. How can I fix the syntax error?

Answer 1

I think there is an answer here.

set foreign key checks off in an insert script

See reply from Adam Machanic.

Quote: Sorry, you can't do that in SQL Server. The best you can do is loop over all of the tables (w/ a cursor, perhaps), alter all constraints, and then set them back at the end. But obviously that would affect all user sessions, not just your current script.

Seems the closest option in SQL Server is something like this.

-- assuming y has FK to x like
-- constraint fk_y_x foreign key (id) references x (id)
alter table y nocheck constraint fk_y_x
go