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.

Merging Side-by-Side Tables

One thing I have always been frustrated with in ASP.Net is that you can't use templated bound controls to produce side-by-side lists within a table. The problem comes from the fact that for each row you need to render the cells from list A, then the cells from list B, and so on. If you want, for example, email addresses to be listed vertically on the left and phone numbers listed on the right, within a single table, you have three options:

1. Display each list in a single cell on a single row, perhaps using one of the list tags. No alternate row styling is available, and you're out of luck if your list needs to be displayed in more than one column.
2. Use templated databound controls to embed subtables within single cells on a single row. If you're fastidious, tweak the margins of the subtable cells to keep the contents properly left-aligned. Live with the fact that unexpected content wrapping would destroy the illusion of synchronized lists and that any alternate row styling will be discontinuous with the parent table.
3. Manually build the cells and rows from the two data sources and add them to the outer table. Track alternate rows and set styles yourself.

Option 3 gives you the look you want plus better HTML, but it's a pain in the neck to do. So one thing I have always wanted to have is a way to merge tables horizontally. Bind your two lists to two separate templated bound controls, and then combine them, matching rows and combining cell collections. To do it, you'd probably have to render each databound control to a string and then merge the generated HTML, which wouldn't be as hard as it sounds if you remember that valid HTML is supposed to be a variant of XML.

Since I am currently somewhat idle I may spend time putting this together. Let me know if you want me to post the results.