List foreign keys with columns in SQL Server database

Query below returns foreign key constrant columns defined in a database.

Check out this summary article of FK queries for SQL Server.


select schema_name(fk_tab.schema_id) + '.' + as foreign_table,
    '>-' as rel,
    schema_name(pk_tab.schema_id) + '.' + as primary_table,
    fk_cols.constraint_column_id as no, as fk_column_name,
    ' = ' as [join], as pk_column_name, as fk_constraint_name
from sys.foreign_keys fk
    inner join sys.tables fk_tab
        on fk_tab.object_id = fk.parent_object_id
    inner join sys.tables pk_tab
        on pk_tab.object_id = fk.referenced_object_id
    inner join sys.foreign_key_columns fk_cols
        on fk_cols.constraint_object_id = fk.object_id
    inner join sys.columns fk_col
        on fk_col.column_id = fk_cols.parent_column_id
        and fk_col.object_id = fk_tab.object_id
    inner join sys.columns pk_col
        on pk_col.column_id = fk_cols.referenced_column_id
        and pk_col.object_id = pk_tab.object_id
order by schema_name(fk_tab.schema_id) + '.' +,
    schema_name(pk_tab.schema_id) + '.' +, 


  • foreign_table - foreign table name with schema name
  • rel - relationship symbol implicating direction
  • primary_table - primary (referenced) table name with schema name
  • no - id of the column in key. Single coumn keys always have 1, composite keys have 1, 2, ... n for each column of the key
  • fk_column_name - foreign table column
  • join - "=" symbol indicating join operation for pair of columns
  • pk_column_name - primary (referenced) table column
  • fk_constraint_name - foreign key constraint name


  • One row represents one foreign key column. If foreign key consists of multiple columns (composite key), each column appears separately.
  • Scope of rows: all foregin keys in a database and their columns
  • Ordered by foreign table schema name and table name and column ordinal posion in key

Sample results

Foreign keys in AdventureWorks database with their columns:

There are no comments. Click here to write the first comment.