Home > Query, SQL > BISQL # 32 : How To find Parent Child Relationship Between Tables in Given Database

BISQL # 32 : How To find Parent Child Relationship Between Tables in Given Database

 

Hi Friends,

Some time when we need to identify Parent – Child Relations between the table for various purpose as follows

  • Insert Data manually by lowest foreign key table
  • Identifying join
  • Understanding Relationships between table for development
  • Finding FK for given table

Even if you think there is no requirement as mentioned above but still I will suggest try to run the query once and see how it show result in easy format

Following is Query

Select 

object_name(rkeyid) Parent_Table, 
object_name(fkeyid) Child_Table, 

object_name(constid) FKey_Name, 

c1.name FKey_Col, 

c2.name Ref_KeyCol
INTO #tblRelationShipTable
From 

sys.sysforeignkeys s

Inner join sys.syscolumns c1

on ( s.fkeyid = c1.id And s.fkey = c1.colid )

Inner join syscolumns c2

on ( s.rkeyid = c2.id And s.rkey = c2.colid )

WHERE object_name(rkeyid) like '%CapProgram%' OR  object_name(rkeyid) LIKE '%productline%'
Order by Parent_Table,Child_Table

SELECT * FROM #tblRelationShipTable Order BY Child_Table

DROP TABLE #tblRelationShipTable
 

Above query have output in following format !!

image 

Hope this helps !!!

You can also Visit to todays Link Resource Website Link Resource # 9 July 30 To July 31 « Dactylonomy of Web Resource

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 33 : Lets Understand Stored Procedure

· BISQL # 34 : What is Identity IN SQL {Identity Part–I}

· BISQL # 35 : Deep dive into Identity Functions in SQL {Identity Part–II}

· BISQL # 36 : Queries to Understand more on Identity aspect {Identity Part–III}

· BISQL # 37 :Checking, Seeding and Reseeding of Identity {Identity Part–IV}

 

Hope you will like this post on Parent child relationship between tables.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog : Link Resource Website

                For More information related to BI World visit my Mentalist Blog

                           Link Resource Blog >> Daily Interesting links

                           SQL Server Mentalist >> SQL Learning Blog

                           Business Intelligence Mentalist >> BI World

                                         Connect With me on

             | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                    Copyright © 2011 – 2012 Vishal Pawar

Advertisements
Categories: Query, SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: