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.

Query

select schema_name(tab.schema_id) + '.' + tab.name as [table],
    col.column_id,
    col.name as column_name,
    case when fk.object_id is not null then '>-' else null end as rel,
    schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table,
    pk_col.name as pk_column_name,
    fk_cols.constraint_column_id as no,
    fk.name 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) + '.' + tab.name,
    col.column_id

Columns

  • 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

Rows

  • 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:

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