SQL Tips by Namwar Rizvi

January 3, 2009

How to find relationship between tables

As a DBA or even as a SQL Server Programmer, you may need a quick script to find relationship between tables. I found a useful script to do that in a SQL Server newsgroup. With some minor update, here is the script which lists the name of the parent table, child table, Referred table, foreing key table and name of the foreign key constraint. I hope you will find it useful. All credit goes to original author.

Select

object_name(rkeyid) Parent_Table,

object_name(fkeyid) Child_Table,

object_name(constid) FKey_Name,

c1.name FKey_Col,

c2.name Ref_KeyCol

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 )

Order by Parent_Table,Child_Table

Advertisements

9 Comments »

  1. This is good

    Comment by ramaesh — March 8, 2011 @ 3:44 PM | Reply

  2. This is good query.
    I like it.

    Comment by Ashish — May 20, 2011 @ 12:13 PM | Reply

  3. nice 1 Namwar Rizvi, saved my day.

    Comment by amol — August 22, 2012 @ 2:55 PM | Reply

  4. There is all the possibility of your ex seeing you in a whole
    new perspective when you have managed to incorporate
    all those changes. When thinking about how to
    get an ex back, it depends if you were the one dumped or if
    you dumped them.

    Comment by Tinyurl.Com — February 1, 2013 @ 4:16 AM | Reply

  5. nice query thanks…

    Comment by ufuk sahin — February 4, 2013 @ 1:47 PM | Reply

  6. Thanks a ton for sharing dude!

    Comment by Roy — May 11, 2015 @ 5:52 PM | Reply

  7. This is awesome. Its helps a lot and save my time.

    Comment by Dave — September 9, 2015 @ 9:05 PM | Reply

  8. hi,can i find the relationship of given tables in sql(it may be direct or indirect relationship of given tables)?. i don’t want all the table in database. i want only some n number of table.(n is 2,3,4,…)

    Comment by sri — June 18, 2016 @ 5:59 AM | Reply

  9. please help me to find indirect relationship of given table name? .my question is already asked above. please help us

    Comment by myideablog — September 10, 2016 @ 4:29 PM | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: