Every Table Related to My Table, Recursively
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.
select constraint_id = f.object_id
, constraint_name = f.name
, parent_name = object_name(f.parent_object_id)
, referenced_object_name = object_name(f.referenced_object_id)
from sys.foreign_keys f
select depth = 1
where referenced_object_name = 'myTable' -- <-- use this to filter results.
select depth = recurse.depth + 1
on fkey.referenced_object_id = recurse.parent_object_id
, parent_column_name = p_ac.name
, 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
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.