archwhite
4/7/2016 - 11:11 AM

Disable foreign key constraints using T-SQL?

Disable foreign key constraints using T-SQL?

-- FOR SPECIFIC TABLE
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
--To re-enable a constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint

-- FOR ALL TABLES (WORKING SLOWLY)
-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
-- enable all constraints
exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"