Here’s some T-SQL code you can use to get a list of all CHECK and foreign key constraints in a SQL Server database.

In the following example, I do a UNION on two database queries. One queries sys.foreign_keys for disabled foreign keys, and the other queries sys.check_constraints for disabled CHECK constraints.

SELECT
  SCHEMA_NAME(schema_id) AS [Schema],
  OBJECT_NAME(parent_object_id) AS 'Table',
  name AS 'Constraint',
  type_desc,
  is_disabled, 
  is_not_trusted
FROM sys.foreign_keys
WHERE is_disabled = 1
UNION
SELECT 
  SCHEMA_NAME(schema_id),
  OBJECT_NAME(parent_object_id),
  name,
  type_desc,
  is_disabled, 
  is_not_trusted
FROM sys.check_constraints
WHERE is_disabled = 1;

Result:

+----------+------------------+--------------------+------------------------+---------------+------------------+
| Schema   | Table            | Constraint         | type_desc              | is_disabled   | is_not_trusted   |
|----------+------------------+--------------------+------------------------+---------------+------------------|
| dbo      | BandMember       | FK_BandMember_Band | FOREIGN_KEY_CONSTRAINT | 1             | 1                |
| dbo      | City             | FK_City_Country    | FOREIGN_KEY_CONSTRAINT | 1             | 1                |
| dbo      | MembershipPeriod | chkValidEndDate    | CHECK_CONSTRAINT       | 1             | 1                |
+----------+------------------+--------------------+------------------------+---------------+------------------+

These are the results I get on one of my test databases in my development environment. This returns all disabled foreign key and CHECK constraints in the current database. To check another database, simply switch to that database and run it there.

Note that when you disable a constraint, the is_not_trusted flag is set to 1, and the constraint is considered untrusted. When you re-enable the constraint, you have the option of resetting it to trusted or leaving it as untrusted. For more information on restoring trust in a constraint, see How to Restore Trust in a Foreign Key Constraint in SQL Server and What You Should Know about WITH NOCHECK when Enabling a CHECK Constraint in SQL Server.