List all tables refrenced by specific table (by FK) in SQL Server database

Query below lists all tables refrenced with foregin key by specific table.

Check out this summary article of FK queries for SQL Server.

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 fk
    inner join sys.tables fk_tab
        on fk_tab.object_id = fk.parent_object_id
    inner join sys.tables 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 (rerefenced) 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 table with provided name (and optionally schema)
  • Ordered by referenced table schema and name

Notes

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

Sample results

All tables referenced with FK by Sales.SalesOrderHeader table in AdventureWorks database.

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