The query below lists all tables that reference a specific table with foreign keys.

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 pk_tab.[name] = 'Your table' -- enter the table name here
--  and schema_name(pk_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 schemas and names - the tables you are looking for
• foreign_table - relationship symbol implicating FK and direction
• primary_table - primary (referenced) table names with schema name - the table you provided as a parameter

Rows

• One row: represents one referencing table
• Scope of rows: all tables referencing a table with the provided name (and optionally the schema)
• Ordered by referencing 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 referencing with FK the Address table in the AdventureWorksLT database.