List table columns with their foreign keys in Azure SQL Database

The query below returns all columns from all tables in a database with a foreign key reference if the column has one.


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 as tab
    inner join sys.columns as col
        on col.object_id = tab.object_id
    left outer join sys.foreign_key_columns as 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 as fk
        on fk.object_id = fk_cols.constraint_object_id
    left outer join sys.tables as pk_tab
        on pk_tab.object_id = fk_cols.referenced_object_id
    left outer join sys.columns as 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 the column in a database
  • column_name - name of the column
  • 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 the AdventureWorksLT database with their foreign 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.