Every Table Related to My Table, Recursively
Someone asked me a few months ago how they could find every table in a database that was related to one particular table. They wanted to know what tables referenced this table via foreign keys, what tables referenced the referencing tables, and so on, from 1 to n.
There’s meta data in SQL Server that can help you do this. Here’s a SQL statement that will find (and number by distance from the original table) all the foreign keys that reference the table, all the tables that reference that table, etc.
This will work – but bear in mind that this a list of tables that reference “MyTable”, and doesn’t include tables that “MyTable” references. To get a list of all tables that reference “MyTable” and vice-versa, recursively, you’d have to do this differently.
If you’d like to see that, let me know: I can work on that.
There’s meta data in SQL Server that can help you do this. Here’s a SQL statement that will find (and number by distance from the original table) all the foreign keys that reference the table, all the tables that reference that table, etc.
;
WITH fkey
as (
select constraint_id = f.object_id
, constraint_name = f.name
, parent_object_id
, parent_name = object_name(f.parent_object_id)
, referenced_object_id
, referenced_object_name = object_name(f.referenced_object_id)
from sys.foreign_keys f
)
, recurse
as (
select depth = 1
, *
from fkey
where referenced_object_name = 'myTable' -- <-- use this to filter results.
union all
select depth = recurse.depth + 1
, fkey.*
from fkey
join recurse
on fkey.referenced_object_id = recurse.parent_object_id
)
, recurseWithFields
as (
select r.*
, parent_column_id
, parent_column_name = p_ac.name
, referenced_column_id
, reference_column_name = r_ac.name
from recurse r
join sys.foreign_key_columns fc
on r.constraint_id = fc.constraint_object_id
join sys.all_columns p_ac
on fc.parent_column_id = p_ac.column_id
and fc.parent_object_id = p_ac.object_id
join sys.all_columns r_ac
on fc.referenced_column_id = r_ac.column_id
and fc.referenced_object_id = r_ac.object_id
)
select *
from recurseWithFields
This will work – but bear in mind that this a list of tables that reference “MyTable”, and doesn’t include tables that “MyTable” references. To get a list of all tables that reference “MyTable” and vice-versa, recursively, you’d have to do this differently.
If you’d like to see that, let me know: I can work on that.