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

Example 1 – Return Only Disabled Foreign Key Constraints

This query returns only the disabled foreign key constraints in the current database. For this example, I only return the name of the constraint, along with its disabled and trusted status.

SELECT 
  name AS 'Constraint',
  is_disabled,
  is_not_trusted
FROM sys.foreign_keys
WHERE is_disabled = 1;

Result:

+------------------------+---------------+------------------+
| Constraint             | is_disabled   | is_not_trusted   |
|------------------------+---------------+------------------|
| FK_BandMember_Band     | 1             | 1                |
| FK_BandMember_Musician | 1             | 1                |
+------------------------+---------------+------------------+

This queries the sys.foreign_keys 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 only enabled foreign key constraints, simply change the 1 to 0.

Note that the is_not_trusted flag is also set to 1 for these disabled constraints. That’s because the system can’t guarantee that the constraint has checked all data. This makes sense, because disabling a constraint opens up the potential for invalid data to enter the database without being checked.

Here’s the same query again, but this time I include the table and the referenced table for each constraint:

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

Result:

+------------------------+------------+--------------------+---------------+------------------+
| Constraint             | Table      | Referenced Table   | is_disabled   | is_not_trusted   |
|------------------------+------------+--------------------+---------------+------------------|
| FK_BandMember_Band     | BandMember | Band               | 1             | 1                |
| FK_BandMember_Musician | BandMember | Musician           | 1             | 1                |
+------------------------+------------+--------------------+---------------+------------------+

Example 2 – Return All Foreign Key Constraints

The following query returns all foreign key constraints for the current database (not just the disabled ones):

SELECT 
  name AS 'Constraint',
  is_disabled,
  is_not_trusted
FROM sys.foreign_keys;

Result:

+--------------------------------+---------------+------------------+
| Constraint                     | is_disabled   | is_not_trusted   |
|--------------------------------+---------------+------------------|
| FK_BandMember_Band             | 1             | 1                |
| FK_BandMember_Musician         | 1             | 1                |
| FK_MembershipPeriod_BandMember | 0             | 0                |
+--------------------------------+---------------+------------------+