In SQL Server, you can use Transact-SQL to return a list of all foreign keys and CHECK constraints for the current database.
The examples on this page query two system views in order to retrieve this information: sys.foreign_keys and sys.check_constraints. You can query each one separately, or use UNION to display them all in a single result set.
Example 1 – Combined Result Set
In this example, I use UNION to return foreign keys and CHECK constraints in the same result set.
SELECT OBJECT_NAME(parent_object_id) AS 'Table', name AS 'Constraint', type_desc, is_disabled, is_not_trusted FROM sys.foreign_keys UNION SELECT OBJECT_NAME(parent_object_id), name, type_desc, is_disabled, is_not_trusted FROM sys.check_constraints;
Result:
+------------------+--------------------------------+------------------------+---------------+------------------+ | Table | Constraint | type_desc | is_disabled | is_not_trusted | |------------------+--------------------------------+------------------------+---------------+------------------| | BandMember | FK_BandMember_Band | FOREIGN_KEY_CONSTRAINT | 0 | 0 | | BandMember | FK_BandMember_Musician | FOREIGN_KEY_CONSTRAINT | 0 | 0 | | City | FK_City_Country | FOREIGN_KEY_CONSTRAINT | 0 | 0 | | MembershipPeriod | chkValidEndDate | CHECK_CONSTRAINT | 0 | 0 | | MembershipPeriod | FK_MembershipPeriod_BandMember | FOREIGN_KEY_CONSTRAINT | 0 | 0 | +------------------+--------------------------------+------------------------+---------------+------------------+
In this case, the current database contains four foreign keys and one CHECK constraint.
We can also see whether each constraint is enabled or disabled, as well as whether it’s trusted or not.
Example 2 – Return All Columns
These two views return more columns than I’ve listed in this example. You can always use a wildcard to return all columns. However, if you do this, you won’t be able to use UNION, because each view returns a different number of columns.
Therefore, you’ll need to query them separately. For example:
SELECT * FROM sys.foreign_keys; SELECT * FROM sys.check_constraints;
That will produce two result sets: one containing the foreign keys, the other containing the CHECK constraints.
To save space, I won’t display the results of that query. But here’s what the second line produces (using vertical output to save you from having to scroll horizontally):
SELECT * FROM sys.check_constraints;
Result (using vertical output):
-[ RECORD 1 ]------------------------- name | chkValidEndDate object_id | 1525580473 principal_id | NULL schema_id | 1 parent_object_id | 1349579846 type | C type_desc | CHECK_CONSTRAINT create_date | 2019-09-11 00:33:02.587 modify_date | 2019-09-11 00:33:02.587 is_ms_shipped | 0 is_published | 0 is_schema_published | 0 is_disabled | 0 is_not_for_replication | 0 is_not_trusted | 0 parent_column_id | 0 definition | ([EndDate]>=[StartDate]) uses_database_collation | 1 is_system_named | 0
You can include any of these columns in your query, but if you combine it with the foreign keys, be sure to include the same columns across both views.
The sys.foreign_keys view returns a few more columns. Here’s a modified query, where I return the first row (by name) from that view.
SELECT TOP(1) * FROM sys.foreign_keys ORDER BY name;
Result (using vertical output):
-[ RECORD 1 ]------------------------- name | FK_BandMember_Band object_id | 1317579732 principal_id | NULL schema_id | 1 parent_object_id | 1285579618 type | F type_desc | FOREIGN_KEY_CONSTRAINT create_date | 2019-08-17 15:58:42.027 modify_date | 2019-08-17 15:58:42.027 is_ms_shipped | 0 is_published | 0 is_schema_published | 0 referenced_object_id | 1253579504 key_index_id | 1 is_disabled | 0 is_not_for_replication | 0 is_not_trusted | 0 delete_referential_action | 0 delete_referential_action_desc | NO_ACTION update_referential_action | 0 update_referential_action_desc | NO_ACTION is_system_named | 0