SQL Server List All Foreign Keys with Referenced Tables

calendar May 21, 2026 / · 7 min read · sql server foreign keys referential integrity database structure sql scripts sys.foreign_keys sys.foreign_key_columns sys.tables sys.columns schema documentation constraint reporting sys.dm_db_index_usage_stats  ·
Share on: linkedin copy

Every database with foreign keys eventually needs a single inventory: every constraint, its parent table and column, and the referenced table and column on the other side. This T-SQL script joins sys.foreign_keys to sys.foreign_key_columns, sys.tables, and sys.columns to produce the canonical foreign-key map of the current database — one row per column in every multi-column foreign key.

Purpose and Overview

The first symptom that a referential boundary has slipped is usually subtle: a DELETE on a parent table that ran in seconds last quarter now takes minutes, or a join across the foreign key plans an unexplained scan on the child side. Almost every diagnosis trails back to the same short list — a constraint disabled by an unattended deployment, a NOT TRUSTED flag left behind by a WITH NOCHECK bulk load, or a duplicate FK shadowing the one the optimizer was relying on. The inventory query below — sys.foreign_keys joined to sys.foreign_key_columns, sys.tables, and sys.columns — produces the complete relationship map in a single pass, so the foreign key in question is one filter away.

The query joins both foreign-key catalog views to sys.tables and sys.columns twice — once on the parent (referencing) side and once on the child (referenced) side. The result is a flat, sortable inventory of every column-level relationship, with the foreign key name, parent table and column, and referenced table and column on each line. Composite keys appear as multiple rows sharing the same ForeignKeyName. Run it in the database you want to audit.

Code Breakdown

The complete script is shown below, followed by an explanation of each section.

 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 has one row per foreign-key constraint in the current database. Important columns: name (the constraint name), object_id (a unique constraint identifier), parent_object_id (the table on the FK side — the referencing table that holds the foreign key column), and referenced_object_id (the table on the PK/UK side — the table being referenced). Note the SQL Server terminology: the "parent" of a foreign key is the referencing table (the child in the conceptual sense), and "referenced" is the referenced table (the parent in the conceptual sense). The naming reflects the FK constraint's syntactic parent (the ALTER TABLE statement), not the relational hierarchy.

sys.foreign_key_columns — One Row per Column in the Constraint

sys.foreign_key_columns carries the column-level detail: constraint_object_id (joins back to sys.foreign_keys.object_id), constraint_column_id (ordinal of the column within a composite key, 1..n), parent_object_id and parent_column_id (the referencing column), referenced_object_id and referenced_column_id (the referenced column). A single-column foreign key produces one row here; a two-column composite foreign key produces two rows, both sharing the same constraint_object_id and differing only in constraint_column_id.

Joining sys.tables and sys.columns Twice — Parent and Child Sides

The script joins sys.tables and sys.columns twice — once with aliases t_parent / c_parent for the referencing side, and once with aliases t_child / c_child for the referenced side. Each sys.columns join uses both object_id and column_id, because column_id values are scoped to their parent table (a column_id of 3 means something different in two different tables). Without the object_id predicate on the join the script would return Cartesian-multiplied wrong matches across tables.

ORDER BY Strategy

ORDER BY t_parent.name, c_parent.name sorts by the referencing table first, then the referencing column. This groups every foreign key on the same parent table together — useful when reviewing "which tables does Orders reference?" or "what columns on Orders are foreign keys?" Swap to ORDER BY t_child.name, fk.name to group instead by the referenced table — useful when investigating "what depends on the Customer table?"

Key Benefits and Use Cases

  • Complete foreign-key inventory: every constraint, every column, in one result set
  • Composite-key safe: multi-column foreign keys appear as multiple rows sharing the same ForeignKeyName — no information is lost
  • Sortable for either dependency direction by changing the ORDER BY: parent-grouped vs child-grouped, depending on what you're investigating
  • Schema documentation: pasted into a wiki or design doc, the output is human-readable "Orders.CustomerID → Customers.CustomerID"-style relationship list
  • Pre-truncate / pre-archive planning: lists every table that references a candidate-for-archive table, so the delete order is unambiguous
  • Reverse-engineer an unfamiliar database: the relationship graph is right there in the output, no SSMS dependency viewer required

Performance Considerations

  • Metadata-only: reads four catalog views and one foreign-key view — no user data scanned. Safe on production at any time
  • Excludes disabled / not-trusted foreign keys? No: every FK appears regardless of enable state. Add WHERE fk.is_disabled = 0 to filter to enabled constraints, or WHERE fk.is_not_trusted = 0 to focus on FKs SQL Server actually uses for optimizer cardinality estimates
  • Excludes cross-database references: foreign keys cannot span databases in SQL Server, so this is not a concern — every reference is local
  • Schema not shown by default: the script projects only table names. Add SCHEMA_NAME(t_parent.schema_id) and SCHEMA_NAME(t_child.schema_id) to the SELECT list for schema-qualified output on multi-schema databases
  • No ON DELETE / ON UPDATE action column: the script does not report cascade/no-action behavior. Add fk.delete_referential_action_desc and fk.update_referential_action_desc to the SELECT list to include them
  • One row per column in composite keys is by design — collapse with FOR XML PATH or STRING_AGG (SQL Server 2017+) if you prefer a comma-separated column list per constraint

Practical Tips

  • For schema-qualified output, extend 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
  • For composite keys as one row each, use STRING_AGG:
    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
    combined with GROUP BY fk.name, t_parent.name, t_child.name
  • For pre-truncate dependency order, build a recursive CTE on top of this query: every truncate target's referenced tables (and the chain upward) become a topological order
  • Combine with sys.dm_db_index_usage_stats on the referencing columns to find unused foreign-key indexes that can be dropped or revised
  • Run after every schema deploy as a regression-detection step — diff against the prior run to catch unintentional foreign-key drops or unintended additions
  • Pair with the "Foreign Keys Without Indexes" audit to ensure every FK has a supporting index on the referencing column — a common cause of slow deletes and join performance issues

Conclusion

The Microsoft Docs canonical foreign-key inventory script is the simplest, most accurate T-SQL probe for every referential relationship in a database. By joining sys.foreign_keys and sys.foreign_key_columns to sys.tables and sys.columns on both sides, it produces a flat relationship map ready for documentation, dependency analysis, or pre-truncate planning. Add schema names, cascade actions, and enable/trust flags as needed — the script structure is small enough to extend in place, and metadata-only so it's safe to run on any production server.

References

Posts in this series