Sunday, July 19, 2015

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 =
        ,      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 =
            ,  referenced_column_id
            ,  reference_column_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: ,


Post a Comment

<< Home