How to drop all FK contraints referencing a table and add them back?

Question

I use this command in SSMS 2016

EXEC sp_fkeys mytable1

and I have over 30 FK constraints referencing to a column in this table. How can I drop all of the constraints in one go, do some truncate work( need to copy a table from one server to another) and add them all back?

Answer 1

I found the answer I need to disable all foreign key constraints first then delete the data like paparazzo said then re-enable them.

Here are the scripts:

Disable Foreign Key Constraints.

DECLARE @sql NVARCHAR(MAX) = N'';   

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' NOCHECK CONSTRAINT ALL;
' FROM x;

EXEC sp_executesql @sql;

Enable them back

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;

EXEC sp_executesql @sql;