Get List of all columns of all tables and Reference table follwled by reference Key of table

Question

Hello I have 50 Tables in Sql server.

All the tables are in join.

For Ex.

Country Table

PKCountryID
Name

State Table

PKStateID
FKCountryID
Name

City Table

PKCityID
FKStateID
Name

Employee Table

PKID
FIrstName
LastName
FKCityID

So i want all the columns of all tables. For that i write below Fiddle.

Fiddle

This will give me result like

COLUMN_NAME

PKEmployeeID
Name
FKCityID

But I want result like this.

COLUMN_NAME

 PKEmployeeID
 Name
-FKCityID
     PKCityID
     Name
    -FKStateID
          PKStateID
          Name
         -FKCountryID
               PKCountryID
               Name
  PKCityID
  Name
 -FKStateID
      PKStateID
      Name
     -FKCountryID
            PKCountryID
            Name

  PKStateID
  Name
 -FKCountryID
       PKCountryID
       Name

  PKCountryID
  Name

OP usually mean Original Post(er) on stack overflow, do you mean to say Output? — Tanner, Jun 24 '14 at 10:40
So do you want your results in a treeview or tabular output? The desired result and tag indicate treeview, but with the fiddle you expect some SQL to be written? — Tanner, Jun 24 '14 at 10:44
I want it in any format. but if it will in tree view that will great help for me. — AB Vyas, Jun 24 '14 at 10:46

score 0 · Accepted Answer · edited May 23 '17 at 11:51

You can try recursive common table expression. Something like:

;
WITH    cte
          AS ( SELECT   c.name AS column_name ,
                       -- t.object_id AS tableid ,
                        0 AS treelevel
               FROM     sys.tables AS t
                        INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
               WHERE    t.object_id = 754101727
               UNION ALL
               SELECT   c.name ,-- + ' ' + f.referenced_column_name AS column_name ,
                        --t.object_id AS tableid ,
                        treelevel + 1
               FROM     ( SELECT    f.name AS foreign_key_name ,
                                    OBJECT_NAME(f.parent_object_id) AS table_name ,
                                    COL_NAME(fc.parent_object_id,
                                             fc.parent_column_id) AS constraint_column_name ,
                                    OBJECT_NAME(f.referenced_object_id) AS referenced_object ,
                                    COL_NAME(fc.referenced_object_id,
                                             fc.referenced_column_id) AS referenced_column_name ,
                                    is_disabled ,
                                    delete_referential_action_desc ,
                                    update_referential_action_desc ,
                                    f.referenced_object_id AS parent_object_id
                          FROM      sys.foreign_keys AS f
                                    INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
                        ) f
                        JOIN cte ON cte.column_name = f.constraint_column_name
                        JOIN sys.tables AS t ON t.object_id = f.parent_object_id
                        INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

                        --IN ORDER TO STOP LOOP AT SPECIFIC LEVEL YOU NEED:
                        WHERE treelevel < 2

             )
    SELECT  
           --IN ORDER TO REMOVE DOUBLINGS ADD DISTINCT
            DISTINCT *
    FROM    cte
    ORDER BY treelevel, tableid

How recursive works you can find here: Sql server CTE and recursion example

It is, if you have loop of references between tables, so will be your tree — Rodion, Jun 24 '14 at 13:27
updated the answer, for tables with many references it will take some time to process — Rodion, Jun 25 '14 at 15:11

Get List of all columns of all tables and Reference table follwled by reference Key of table

1 Answers1