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 !!
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
Copyright © 2011 – 2012 Vishal Pawar
-
July 31, 2011 at 10:11 pmList of monthly post of MS BI and SQL blog–July 2011 « SQL Server Mentalist
-
July 31, 2011 at 10:19 pmList of monthly post of MS BI and SQL blog–July 2011 « (B)usiness (I)ntelligence Mentalist
-
July 31, 2011 at 10:30 pmList of monthly post of MS BI and SQL blog–July 2011 « Dactylonomy of Web Resource
SQL Server Mentalist 