SQL Server List All Foreign Keys with Referenced Tables

Every database with foreign keys eventually needs a single inventory: each constraint, the table and column that hold it, and the table and column it points at. This T-SQL script joins sys.foreign_keys to sys.foreign_key_columns, sys.tables, and sys.columns to produce that map for the current database, with one row per column in every foreign key.

Purpose and Overview

A developer asks which tables will break if the Customers table is archived, and the only honest answer is "run the query and read the list." A database with hundreds of tables needs the relationships as rows you can sort, filter, diff against last month's output, or paste into a design document. The catalog views hold all of it already; the script only has to join them correctly.

The query joins the two foreign-key catalog views to sys.tables and sys.columns twice, once for the referencing side and once for the referenced side. Each output row carries the foreign key name, the referencing table and column, and the referenced table and column. A composite foreign key appears as several rows that share one ForeignKeyName. Run it in the database you want to audit.

Reading the output takes one piece of vocabulary. In the catalog views the "parent" of a foreign key constraint is the referencing object, the table that holds the foreign key column. The "referenced" object is the table that holds the candidate key. That is the reverse of how many people use "parent" and "child" in conversation, which is why the script aliases the two sides t_parent and t_child yet labels the second pair Referenced in the output. Keep the catalog meaning in mind when you read the column headers.

Code Breakdown

 1SELECT fk.name           AS ForeignKeyName,
 2       t_parent.name     AS ParentTableName,
 3       c_parent.name     AS ParentColumnName,
 4       t_child.name      AS ReferencedTableName,
 5       c_child.name      AS ReferencedColumnName
 6FROM   sys.foreign_keys fk
 7INNER JOIN sys.foreign_key_columns fkc
 8       ON fkc.constraint_object_id = fk.object_id
 9INNER JOIN sys.tables t_parent
10       ON t_parent.object_id = fk.parent_object_id
11INNER JOIN sys.columns c_parent
12       ON fkc.parent_column_id = c_parent.column_id
13       AND c_parent.object_id = t_parent.object_id
14INNER JOIN sys.tables t_child
15       ON t_child.object_id = fk.referenced_object_id
16INNER JOIN sys.columns c_child
17       ON c_child.object_id = t_child.object_id
18       AND fkc.referenced_column_id = c_child.column_id
19ORDER BY t_parent.name, c_parent.name;

sys.foreign_keys: One Row per Constraint

sys.foreign_keys contains a row per object that is a FOREIGN KEY constraint, sys.objects.type = F. It inherits the sys.objects columns, which is where name, object_id, and parent_object_id come from, and adds referenced_object_id, key_index_id, is_disabled, is_not_for_replication, is_not_trusted, the delete and update referential action columns with their _desc counterparts, and is_system_named. In this script fk.object_id identifies the constraint, fk.parent_object_id points to the table that holds the foreign key, and fk.referenced_object_id points to the table that holds the candidate key.

sys.foreign_key_columns: One Row per Column

sys.foreign_key_columns has a row for each column, or set of columns, that comprise a foreign key. Its constraint_object_id joins back to sys.foreign_keys.object_id. constraint_column_id numbers the columns from 1 to n, where n is the number of columns in the key. parent_column_id is the referencing column, and referenced_column_id is the referenced candidate key column. A single-column foreign key produces one row here. A two-column key produces two rows with the same constraint_object_id and different constraint_column_id values.

Joining sys.tables and sys.columns Twice

sys.tables returns a row for each user table, so the script resolves both object_id values to table names through it. The two joins to sys.columns each use object_id and column_id together. column_id is unique only within an object, so a column_id of 3 means a different column in every table, and a join on column_id alone would pair unrelated columns. The object_id predicate pins each column lookup to the correct table.

ORDER BY Strategy

ORDER BY t_parent.name, c_parent.name sorts by the referencing table and then its column, which answers "what does Orders reference?" Swapping to ORDER BY t_child.name, fk.name groups by the referenced table instead, which answers "what depends on Customers?" and is the version to use before archiving or truncating anything.

A Caveat on Names

The output shows only table names. sys.objects exposes schema_id, so two tables with the same name in different schemas look identical in the result. On a database that uses several schemas, add SCHEMA_NAME(t_parent.schema_id) and SCHEMA_NAME(t_child.schema_id) to the SELECT list.

Key Benefits and Use Cases

  • Complete foreign-key inventory: every constraint and every column in one result set, with no diagram tool required
  • Composite-key safe: multi-column foreign keys appear as several rows that share a ForeignKeyName, so no column is lost
  • Either dependency direction: change the ORDER BY to group by referencing table or by referenced table
  • Schema documentation: the output reads as a relationship list such as Orders.CustomerID to Customers.CustomerID, ready for a wiki or design document
  • Archive and delete planning: filter on the referenced table to list every table that must be handled first
  • Unfamiliar databases: the relationship graph is visible in the first query you run against an inherited system
  • Naming review: adding fk.is_system_named to the SELECT list flags constraints whose name was generated by the system (1) rather than supplied by a user (0), which makes a naming-convention cleanup a filter instead of a hunt through scripts
  • Self-referencing tables: a table with a foreign key to itself shows the same table name on both sides of the row, such as an employee table pointing at its own manager column, so hierarchies are easy to spot

Performance Considerations

  • Catalog views only: the query reads sys.foreign_keys, sys.foreign_key_columns, sys.tables, and sys.columns. It never reads user table data, so run time depends on the number of objects, not rows.
  • Metadata visibility: catalog view visibility is limited to securables that a user either owns or on which the user was granted some permission. A login that holds no permission on some tables may therefore see a shorter list, so audit with an account that can see every table.
  • Disabled and untrusted keys are included: the script does not filter on is_disabled or is_not_trusted. Add WHERE fk.is_disabled = 0 to show only enabled constraints, or WHERE fk.is_not_trusted = 1 to list the ones to review.
  • Trust matters to the optimizer: If is_not_trusted = 1, the foreign key does not check existing data when it is re-enabled or re-created, and the query optimizer is therefore unable to consider potential performance improvements. Trusted foreign keys can be used to simplify execution plans.
  • Cascade behavior is not shown: add fk.delete_referential_action_desc and fk.update_referential_action_desc to see NO_ACTION, CASCADE, SET_NULL, or SET_DEFAULT per constraint.
  • Row-per-column output: a composite key repeats its name on several rows by design. Use STRING_AGG if you want one line per constraint.

Practical Tips

  • Add schema, actions, and state to the output by extending the SELECT list:
     1SELECT SCHEMA_NAME(t_parent.schema_id) AS ParentSchema,
     2       t_parent.name AS ParentTableName,
     3       c_parent.name AS ParentColumnName,
     4       SCHEMA_NAME(t_child.schema_id)  AS ReferencedSchema,
     5       t_child.name  AS ReferencedTableName,
     6       c_child.name  AS ReferencedColumnName,
     7       fk.name       AS ForeignKeyName,
     8       fk.delete_referential_action_desc AS OnDelete,
     9       fk.update_referential_action_desc AS OnUpdate,
    10       fk.is_disabled,
    11       fk.is_not_trusted
    
  • Collapse composite keys to one row each with STRING_AGG, which is available in SQL Server 2017 and later:
    1STRING_AGG(c_parent.name, ',') WITHIN GROUP (ORDER BY fkc.constraint_column_id) AS ParentColumns,
    2STRING_AGG(c_child.name,  ',') WITHIN GROUP (ORDER BY fkc.constraint_column_id) AS ReferencedColumns
    
    Combine it with GROUP BY fk.name, t_parent.name, t_child.name (and the schema expressions if you added them).
  • Fix untrusted keys deliberately: when existing data complies with the constraint, re-check it with ALTER TABLE ... WITH CHECK CHECK CONSTRAINT <name>. Run it in a maintenance window, because it validates the existing rows.
  • Mind the 253 limit: a table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 raises the limit for incoming references to 10,000, supported only for DELETE operations. This query's output lets you count both directions per table.
  • Check the supporting indexes: creating a foreign key constraint does not automatically create a corresponding index, unlike a primary key. A manually created index on a foreign key is often useful because those columns are frequently used in join criteria, so compare this inventory against your index list.
  • Snapshot and diff after each deployment: save the output and compare it with the previous run to catch foreign keys that were dropped or added unintentionally.

Conclusion

This query is the standard example for sys.foreign_key_columns, and it remains the simplest complete answer to "what references what" in a SQL Server database. Add schema names, referential actions, and the disabled and trusted flags to fit the audit, and keep the output where you can diff it, since foreign key changes are easy to miss without it.

References

Posts in this series