List table columns with their foreign keys in SQL Server database

Query below returns all columns from all tables in a database with a foreign key refererence if column has one.

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


select schema_name(tab.schema_id) + '.' + as [table],
    col.column_id, as column_name,
    case when fk.object_id is not null then '>-' else null end as rel,
    schema_name(pk_tab.schema_id) + '.' + as primary_table, as pk_column_name,
    fk_cols.constraint_column_id as no, as fk_constraint_name
from sys.tables tab
    inner join sys.columns col 
        on col.object_id = tab.object_id
    left outer join sys.foreign_key_columns fk_cols
        on fk_cols.parent_object_id = tab.object_id
        and fk_cols.parent_column_id = col.column_id
    left outer join sys.foreign_keys fk
        on fk.object_id = fk_cols.constraint_object_id
    left outer join sys.tables pk_tab
        on pk_tab.object_id = fk_cols.referenced_object_id
    left outer join sys.columns pk_col
        on pk_col.column_id = fk_cols.referenced_column_id
        and pk_col.object_id = fk_cols.referenced_object_id
order by schema_name(tab.schema_id) + '.' +,


  • table - table in a database with schema name
  • column_id - number of column in a database
  • column_name - column name
  • rel - relationship symbol ('>-') indicating foreign key and direction
  • primary_table - referenced table
  • pk_column_name - referenced column
  • no - column id in a key constraint
  • fk_constraint_name - foreign key constraint name


  • One row represents one column of every table in a database
  • Scope of rows: all columns from all tables in a database
  • Ordered by table schema and name, column id in a table

Sample results

A few columns in AdventureWorks database with their foregin keys:

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