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:

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.