Get the hierarchy orders(top to bottom) of the table

  • Can we get the hierarchy orders of table

    Eg: 10 tables

    Table1 is master to table2 and so on till table10

    Just want the parent table name ,key name, child name ,child key in the order

    parent name,key name, child name ,child key

    p1 k1 p2 k2

    p2 k2 p3 k3

    .

    .

    .

    when i join with sysobjects and sysforeignkeys theay are not in orders(Top to bottom),they are giving the links b/w tables not in the top to bottom

    Thanks!

  • My question would be, why do people insist on storing hierarchical data in multiple tables? It's usually SO much easier to store/maintain the data as a single adjacency list and then build nested sets to query with. Please see the following articles.

    http://www.sqlservercentral.com/articles/Hierarchy/94040/

    http://www.sqlservercentral.com/articles/T-SQL/94570/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (4/1/2014)


    My question would be, why do people insist on storing hierarchical data in multiple tables? It's usually SO much easier to store/maintain the data as a single adjacency list and then build nested sets to query with. Please see the following articles.

    http://www.sqlservercentral.com/articles/Hierarchy/94040/

    http://www.sqlservercentral.com/articles/T-SQL/94570/

    I cant keep all my transaction in a single table right?

    eg : employee and manager relationship

    to to store all those information we need atleast 8 to 10 tables like emp->mgr emp->dept dept->region and so on....

    all these information we cant keep in single table right.

    so my question was how to find the relationship from top to bottom at constraints level not at the data level.

    Thanks!

  • Ok... so what's the structure of the tables for the emp->mgr relationship?

    Shifting gears, because of all the tables you have involved, you really need to read and heed the first link under "Helpful Links" in my signature line below.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Just want the parent table name ,key name, child table name ,child key in the order

    parent name,key name, child name ,child key

    p1 k1 p2 k2

    p2 k2 p3 k3

    .

    .

    .

    when i join with sysobjects and sysforeignkeys theay are not in orders(Top to bottom),they are giving the links b/w tables not in the top to bottom

  • yuvipoy (4/2/2014)


    Just want the parent table name ,key name, child table name ,child key in the order

    parent name,key name, child name ,child key

    p1 k1 p2 k2

    p2 k2 p3 k3

    .

    .

    .

    when i join with sysobjects and sysforeignkeys theay are not in orders(Top to bottom),they are giving the links b/w tables not in the top to bottom

    Yep... I get that. But I don't know a thing about the data in the tables and you haven't provided any information on how to possibly join them. Without such information, my answer can only be to "lookup the ParentID in Table2 and use it to find the information in Table1".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (4/2/2014) my answer can only be to "lookup the ParentID in Table2 and use it to find the information in Table1".

    Yes similarly kind fof information i need but how to get them form sysforeignkey and sysobjects?

    I know only my top node that is parent table name, i need to get all the brach child name.

    how to find the interrelationship.

  • yuvipoy (4/3/2014)


    Jeff Moden (4/2/2014) my answer can only be to "lookup the ParentID in Table2 and use it to find the information in Table1".

    Yes similarly kind fof information i need but how to get them form sysforeignkey and sysobjects?

    I know only my top node that is parent table name, i need to get all the brach child name.

    how to find the interrelationship.

    Sorry for the late reply.

    The easiest way would be to open the tables in the design mode of SSMS and look at the FK's.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (4/5/2014)

    The easiest way would be to open the tables in the design mode of SSMS and look at the FK's.

    This will give about the sigle relationship, i am looking for many to many relationship.

  • I've never tried a database diagram but it might be useful in this case to visualize the entity relationships.

    ----------------------------------------------------

  • yuvipoy (4/8/2014)


    Jeff Moden (4/5/2014)

    The easiest way would be to open the tables in the design mode of SSMS and look at the FK's.

    This will give about the sigle relationship, i am looking for many to many relationship.

    Ah... Now I understand your question. This is about the relationship of all tables in a database? If so, do you have proper foreign keys on every table that has a relationship with another table?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • yuvipoy (4/3/2014)


    Jeff Moden (4/2/2014) my answer can only be to "lookup the ParentID in Table2 and use it to find the information in Table1".

    Yes similarly kind fof information i need but how to get them form sysforeignkey and sysobjects?

    I know only my top node that is parent table name, i need to get all the brach child name.

    how to find the interrelationship.

    hey

    check if this helps

    DECLARE @MasterTableName AS VARCHAR(255)

    set @MasterTableName='parent_table_name'

    ;WITH ParentChi AS

    (

    --initialization

    SELECT object_name(referenced_object_id) as ParentTable, object_name(parent_object_id) as ChildTable, 1 as hlevel

    FROM sys.foreign_keys

    WHERE object_name(referenced_object_id) =@MasterTableName

    UNION ALL

    --recursive execution

    SELECT object_name(e.referenced_object_id), object_name(e.parent_object_id),m.hlevel + 1

    FROM sys.foreign_keys e INNER JOIN ParentChi m

    ON object_name(e.referenced_object_id) = m.ChildTable --.object_name(parent_object_id)

    and not (m.ChildTable = object_name(e.parent_object_id) and m.ParentTable = object_name(e.referenced_object_id))

    ---Used this because Might Go in Infinite if some table is self Referenced

    )

    SELECT distinct childtable, hlevel FROM ParentChi

    order by hlevel desc

    option (maxrecursion 32767)

  • find the updated one

    DECLARE @MasterTableName AS VARCHAR(255)

    set @MasterTableName='parent_table_name'

    ;WITH ParentChi AS

    (

    --initialization

    SELECT

    OBJECT_NAME (fkey.referenced_object_id) AS ParentTable

    ,COL_NAME(fcol.referenced_object_id,fcol.referenced_column_id) AS ReferenceColumnName

    ,OBJECT_NAME(fkey.parent_object_id) AS ChildTable

    ,COL_NAME(fcol.parent_object_id, fcol.parent_column_id) AS ColumnName

    ,1 as hlevel

    FROM sys.foreign_keys AS fkey

    INNER JOIN sys.foreign_key_columns AS fcol ON fkey.OBJECT_ID = fcol.constraint_object_id

    where

    OBJECT_NAME (fkey.referenced_object_id) =@MasterTableName

    UNION ALL

    --recursive execution

    SELECT

    OBJECT_NAME (e.referenced_object_id) AS ParentTable

    ,COL_NAME(e.referenced_object_id,fcol.referenced_column_id) AS ReferenceColumnName

    ,OBJECT_NAME(e.parent_object_id) AS ChildTable

    ,COL_NAME(fcol.parent_object_id, fcol.parent_column_id) AS ColumnName

    ,m.hlevel + 1 as hlevel

    FROM sys.foreign_keys AS e

    INNER JOIN sys.foreign_key_columns AS fcol ON e.OBJECT_ID = fcol.constraint_object_id

    INNER JOIN ParentChi m

    ON object_name(e.referenced_object_id) = m.ChildTable --.object_name(parent_object_id)

    and not (m.ChildTable = object_name(e.parent_object_id) and m.ParentTable = object_name(e.referenced_object_id))

    ----Used this because Might Go in Infinite if some table is self Referenced

    )

    SELECT distinct ParentTable, ReferenceColumnName,ChildTable,ColumnName,hlevel FROM ParentChi

    order by hlevel desc

    option (maxrecursion 32767)

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply