The query below lists all tables referenced with a foreign key per specific table.

Query

select distinct
    schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
    '>-' as rel,
    schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table
from sys.foreign_keys as fk
    inner join sys.tables as fk_tab
        on fk_tab.object_id = fk.parent_object_id
    inner join sys.tables as pk_tab
        on pk_tab.object_id = fk.referenced_object_id
where fk_tab.[name] = 'Your table' -- enter table name here
--  and schema_name(fk_tab.schema_id) = 'Your table schema name'
order by schema_name(fk_tab.schema_id) + '.' + fk_tab.name,
    schema_name(pk_tab.schema_id) + '.' + pk_tab.name

Columns

• foreign_table - foreign table name with schema name - the table you provided as a parameter
• foreign_table - relationship symbol implicating FK and direction
• primary_table - primary (referenced) tables names with schema name - the tables you are looking for

Rows

• One row: represents one referenced table
• Scope of rows: all tables referenced by the table that has the provided name (and optionally the schema)
• Ordered by: referenced table schema and name

Notes

• There can be more tables with the same name. If that's the case, uncomment the where clause and provide a schema name

Sample results

All tables referenced with FK by Product table in the AdventureWorksLT database.