You can use the code below to disable all CHECK and foreign key constraints for the current database in SQL Server.

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

This uses Microsoft’s undocumented sp_MSforeachtable stored procedure. This procedure allows you to perform tasks against each table in a database. So it’s perfect for our task here – to disable all CHECK constraints within the current database.

Below is an example where I do this and then check the result.

Example 1 – Review the Constraints

First, I’ll take a quick look at the current CHECK and foreign key constraints in the database, to see whether or not they are enabled or disabled.

SELECT
  OBJECT_NAME(parent_object_id) AS 'Table',
  name AS 'Constraint',
  is_disabled, 
  is_not_trusted
FROM sys.foreign_keys
UNION
SELECT 
  OBJECT_NAME(parent_object_id),
  name,
  is_disabled, 
  is_not_trusted
FROM sys.check_constraints;

Result:

+----------------+-----------------+---------------+------------------+
| Table          | Constraint      | is_disabled   | is_not_trusted   |
|----------------+-----------------+---------------+------------------|
| ConstraintTest | chkPrice        | 0             | 0                |
| ConstraintTest | chkValidEndDate | 0             | 0                |
| ConstraintTest | chkTeamSize     | 0             | 0                |
| Occupation     | chkJobTitle     | 0             | 0                |
+----------------+-----------------+---------------+------------------+

So there are currently four CHECK constraints in the database, for two different tables.

We can see that all constraints are enabled because is_disabled is set to 0.

Example 2 – Disable the Constraints

Now I’ll disable all constraints:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

It’s usually a good idea to make sure you’re using the correct database when doing stuff like this. So we could add to the above code by explicitly switching to the correct database:

USE Test;
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

In this case I switch to a database called Test.

Example 3 – Check the Result

Having run the above code, now I’ll run the same query from the first example to see the result.

SELECT
  OBJECT_NAME(parent_object_id) AS 'Table',
  name AS 'Constraint',
  is_disabled, 
  is_not_trusted
FROM sys.foreign_keys
UNION
SELECT 
  OBJECT_NAME(parent_object_id),
  name,
  is_disabled, 
  is_not_trusted
FROM sys.check_constraints;

Result:

+----------------+-----------------+---------------+------------------+
| Table          | Constraint      | is_disabled   | is_not_trusted   |
|----------------+-----------------+---------------+------------------|
| ConstraintTest | chkPrice        | 1             | 1                |
| ConstraintTest | chkValidEndDate | 1             | 1                |
| ConstraintTest | chkTeamSize     | 1             | 1                |
| Occupation     | chkJobTitle     | 1             | 1                |
+----------------+-----------------+---------------+------------------+

So all constraints in the database have been disabled (because the is_disabled column is set to 1 for those constraints).

Notice that the is_not_trusted column is also set to 1. This is an important consideration, especially if you intend to re-enable any of your disabled constraints.

See What You Should Know about WITH NOCHECK when Enabling a CHECK Constraint in SQL Server for information about how to restore trust when re-enabling your constraints. The information in that article also applies to foreign keys.

Disable the Constraints Individually

If you only want to disable the constraints one-by-one, see How to Disable a CHECK Constraint in SQL Server for examples.