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

By “untrusted”, I’m referring to those constraints that have their is_not_trusted flag set to 1.

Example 1 – Return Only Untrusted CHECK Constraints

This query returns only the untrusted CHECK constraints in the current database.

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

Result:

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

This query returns the constraint name, the name of the table that it’s applied to, and the constraint’s definition.

It also returns the is_disabled column. This tells us whether or not the constraint is currently enabled or disabled. This can be important to know, because a constraint can be enabled but untrusted at the same time.

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

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

Example 2 – Return Trust Status

Here it is again, but this time I swap the definition column with the is_not_trusted column:

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

Result:

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

It’s probably superfluous to include the is_not_trusted column, but at least it helps to reiterate the fact that an enabled constraint can still be untrusted.

Example 3 – Return All CHECK Constraints

The following query returns all CHECK constraints for the current database (not just the untrusted ones):

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             | 1                |
| ConstraintTest | chkValidEndDate | 1             | 1                |
| ConstraintTest | chkTeamSize     | 0             | 0                |
| Occupation     | chkJobTitle     | 1             | 1                |
+----------------+-----------------+---------------+------------------+

For a detailed discussion (and examples) of the is_not_trusted flag, see What You Should Know about WITH NOCHECK when Enabling a CHECK Constraint in SQL Server.