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: