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.
;
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.
Labels: sql, tables