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.CustomerIDtoCustomers.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_namedto 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, andsys.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_disabledoris_not_trusted. AddWHERE fk.is_disabled = 0to show only enabled constraints, orWHERE fk.is_not_trusted = 1to 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_descandfk.update_referential_action_descto 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_AGGif 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:Combine it with1STRING_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 ReferencedColumnsGROUP 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
- sys.foreign_key_columns (Transact-SQL) — column definitions for the view and the example inventory query used here
- sys.foreign_keys (Transact-SQL) — constraint-level columns including
is_disabled,is_not_trusted, and the referential action descriptions - sys.tables (Transact-SQL) — the catalog view that returns a row for each user table
- sys.columns (Transact-SQL) — per-column catalog view used twice in the join
- sys.objects (Transact-SQL) — the inherited columns
object_id,parent_object_id, andschema_id - Disable foreign key constraints with INSERT and UPDATE statements — trusted versus untrusted foreign keys and how to re-check them
- Primary and foreign key constraints — reference limits, cascading actions, and indexing guidance for foreign keys
- STRING_AGG (Transact-SQL) — the aggregate used to collapse composite keys
Posts in this series
- sp_pkeys: SQL Server Primary Key Discovery Script
- SQL Server: Find Tables with Clustered Indexes for Rebuild
- SQL Server: Find Tables Without Primary Keys
- SQL Server Foreign Keys Without Indexes Script
- SQL Server List All Foreign Keys with Referenced Tables
- SQL Server Find Columns by Data Type Across the Database
- SQL Server Catalog Views: sys.tables, sys.indexes, sys.objects
- sysindexes vs sys.indexes: Legacy and Modern Catalog Views
- sp_pkeys and Primary Key Metadata in SQL Server
- SQL Server Database Schema and Data Dictionary Queries
- System-Versioned (Temporal) Tables in SQL Server