If you need to return a list of all CHECK constraints that have been disabled in a SQL Server database, you can run the T-SQL code below.

Example 1 – Return Only Disabled CHECK Constraints

This query returns only the disabled CHECK constraints in the current database. It returns the constraint name, the name of the table that it’s applied to, and the constraint’s definition.

SELECT 
  OBJECT_NAME(parent_object_id) AS 'Table',
  name AS 'Constraint',
  definition
FROM sys.check_constraints
WHERE is_disabled = 1;

Result:

+----------------+-----------------+-------------------------------+
| Table          | Constraint      | definition                    |
|----------------+-----------------+-------------------------------|
| ConstraintTest | chkValidEndDate | ([EndDate]>=[StartDate])      |
| Occupation     | chkJobTitle     | ([JobTitle]<>'Digital Nomad') |
+----------------+-----------------+-------------------------------+

This queries the sys.check_constraints system view. We know it only returns disabled constraints because the WHERE clause specifies only rows that have the is_disabled column set to 1.

If you want to return all enabled CHECK constraints, simply change the 1 to 0.

Example 2 – Return All CHECK Constraints

The following query returns all CHECK constraints for the current database (not just the disabled ones). This time I return the is_disabled column in order to demonstrate where the previous query got its value from:

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

Result:

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

I’ve also included the is_not_trusted column in this query. It’s prudent to be mindful of this value, because a constraint can remain untrusted even after it’s been re-enabled. For a detailed discussion (and examples) of this flag, see What You Should Know about WITH NOCHECK when Enabling a CHECK Constraint in SQL Server.