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

Question

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

yuvipoy

SSChampion

Points: 10469
More actions
April 1, 2014 at 8:16 am

#285290

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!

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

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

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)

yuvipoy SSChampion Points: 10469 More actions · Answer 2

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!

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

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)

yuvipoy SSChampion Points: 10469 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

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)

yuvipoy SSChampion Points: 10469 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

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)

yuvipoy SSChampion Points: 10469 More actions · Answer 8

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.

MMartin1 One Orange Chip Points: 27799 More actions · Answer 9

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

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 10

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)

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 11

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)

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 12

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)