How to turn off all constraints in code

Very often when we migrate data, like with OM and the Shangai incident, we need to be bale to turn off all check contraints to be able to truncate data in tables. To do this we can use the gui and that takes a lot of time. A better way is to use this code:

-- To turn off all check contrains(e.g forreign keys)
ALTER TABLE VDS_VehicleMaster NOCHECK CONSTRAINT ALL


-- Enable all table constraints
ALTER TABLE VDS_VehicleMaster CHECK CONSTRAINT ALL

Note, in many cases you also need to do the same on the other tables involved in the realtionship. This saved me hours when migrating the Shanghai data.


To do it on all tables at once we can use this code:

exec sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

And to turn it on again

exec sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'