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.

;

 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.