locked
Need tables list in Parent child level order RRS feed

  • Question

  • Hello All,

    I am working on one of the data load task where I am looking to find tables' list dynamically in parent-child level. 

    I want to get the list of parent tables first then dependents child tables list later, I have 100+ tables in DB which needs to be ordered in the mentioned way. Appreciate the suggestion if its possible.

    Thanks

    Friday, January 3, 2020 6:41 PM

All replies

  • This isn't exactly what you are wanting but is this on the right track?

    SELECT
        o1.name AS FK_table,
        c1.name AS FK_column,
        fk.name AS FK_name,
        o2.name AS PK_table,
        c2.name AS PK_column,
        pk.name AS PK_name,
        fk.delete_referential_action_desc AS Delete_Action,
        fk.update_referential_action_desc AS Update_Action
    FROM sys.objects o1
        INNER JOIN sys.foreign_keys fk
            ON o1.object_id = fk.parent_object_id
        INNER JOIN sys.foreign_key_columns fkc
            ON fk.object_id = fkc.constraint_object_id
        INNER JOIN sys.columns c1
            ON fkc.parent_object_id = c1.object_id
            AND fkc.parent_column_id = c1.column_id
        INNER JOIN sys.columns c2
            ON fkc.referenced_object_id = c2.object_id
            AND fkc.referenced_column_id = c2.column_id
        INNER JOIN sys.objects o2
            ON fk.referenced_object_id = o2.object_id
        INNER JOIN sys.key_constraints pk
            ON fk.referenced_object_id = pk.parent_object_id
            AND fk.key_index_id = pk.unique_index_id
    ORDER BY o1.name, o2.name, fkc.constraint_column_id

    Friday, January 3, 2020 6:52 PM
  • Unfortunately no. This doesnt give me the required order i tried this earlier.

    Porus

    Friday, January 3, 2020 7:08 PM
  • Where is the DDL for this table? Why do we have any sample data? I wrote an entire book on different ways of representing hierarchical data in SQL. If you happen to be using the nested set model, this is incredibly easy

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, January 3, 2020 7:27 PM
  • ;With cteForeignKey As
    (Select schema_name(t.schema_id) As SchemaName, t.name As TableName, t.object_id, 1 As Level
    From sys.tables t
    Where Not Exists(Select * From sys.foreign_key_columns f Where t.object_id = f.referenced_object_id)
    Union All
    Select schema_name(f.schema_id) As SchemaName, object_name(f.referenced_object_id) As TableName, f.referenced_object_id, Level + 1 As Level
    From cteForeignKey c
    Inner Join sys.foreign_keys f On c.object_id = f.parent_object_id),
    
    cteMaxLevel As
    (Select cf.SchemaName, cf.TableName, Max(cf.Level) As Level 
    From cteForeignKey cf
    Group By cf.SchemaName, cf.TableName)
    
    Select cm.SchemaName, cm.TableName
    From cteMaxLevel cm
    Order by cm.Level, cm.SchemaName, cm.TableName;

    Tom
    • Proposed as answer by Naomi N Sunday, January 5, 2020 9:46 PM
    Saturday, January 4, 2020 6:28 AM
  • Where is the DDL for this table?

    Celko, you can start here: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-foreign-keys-transact-sql?view=sql-server-ver15


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, January 4, 2020 10:21 AM
  • Good day Aneeshporus/PorusAis

    1. Are you the same person?!?

    One user opened the thread and the other responded like he is the same user

    2. Please clarify why the solution that rvsc48 provided does not fit you

    what do you mean by "doesnt give me the required order" ?!?

    If the result set is as expected then you ca use ORDER BY in order to get different order

    In first glance seems to me that  the solution that rvsc48 provides what you asked for


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, January 4, 2020 6:51 PM
  • what do you mean by "doesnt give me the required order" ?!?

    If the result set is as expected then you ca use ORDER BY in order to get different order

    Ronen, did you even read the original question? PorusAis asked for tables being listed in parent-child level. rvsc48 posted a flat dump of sys.foriegn_keys and related views, and there is no ORDER BY clause that can make it return the desired result.

    To produce the answer, we need a recursive query, and Tom Cooper's post meets that requirement. However, when I tested it, it died with infinite recursion in all databases I tried it in. Eventually, I realised that this is due to that Tom had overlooked self-referencing tables. Below is a version that addresses this problem. It still dies with infinite recursion in two of my test databases, but in these databases I have tables that have foreign keys to each other, which is a little more unusual. And a little more difficult to deal with.

    ;With cteForeignKey As
    (Select schema_name(t.schema_id) As SchemaName, t.name As TableName, t.object_id, 1 As Level
    From sys.tables t
    Where Not Exists(Select * From sys.foreign_key_columns f Where t.object_id = f.referenced_object_id)
    Union All
    Select schema_name(f.schema_id) As SchemaName, object_name(f.referenced_object_id) As TableName, f.referenced_object_id, Level + 1 As Level
    From cteForeignKey c
    Inner Join sys.foreign_keys f On c.object_id = f.parent_object_id
    where f.parent_object_id <> f.referenced_object_id
    ),
    cteMaxLevel As
    (Select cf.SchemaName, cf.TableName, Max(cf.Level) As Level From cteForeignKey cf
    Group By cf.SchemaName, cf.TableName)

    Select cm.Level, cm.SchemaName, cm.TableName
    From cteMaxLevel cm
    Order by cm.Level, cm.SchemaName, cm.TableName;


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Naomi N Sunday, January 5, 2020 9:47 PM
    Saturday, January 4, 2020 11:00 PM
  • Thank you, this is what I needed.

    Porus

    Tuesday, January 7, 2020 10:33 AM
  • I had requirement once in one of our webapplication, i used recursive query

    With Parentchildhierarchy(ParentID, ChildID, Levels)
    As(
      Select ParentID, ChildID, 0 as Levels
      From temp
      Where ParetnID = 2
      Union All
      Select p.ParentID, p.ChildID, Levels + 1
      From temp p
      Join Parentchildhierarchy cte On cte.ParentID= p.ChildID
    )
    Select *
    From Parentchildhierarchy

    Tuesday, January 7, 2020 4:48 PM
  • Please close the thread by marking Erland's response as answer (as you said, his solution solved you need)

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, January 7, 2020 11:00 PM
  • Please close the thread by marking Erland's response as answer (as you said, his solution solved you need)

    Don't forget Tom's post! He did the hard work.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, January 7, 2020 11:06 PM
  • Hi ,

     

    We are glad to hear that they are helpful to you. If you have solved your issue ,please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 8, 2020 6:18 AM