Get relationship between tables using foreign key relationships!

SELECT Referencing_Object_name, Referencing_Column_Name, Referenced_Object_Name, Referenced_Column_Name FROM 

(SELECT Referenced_Column_Name = c.name, Referenced_Object_name = o.name, f.constid FROM sysforeignkeys f, sysobjects o, syscolumns c WHERE (f.rkeyid = o.id) AND c.id = o.id AND c.colid = f.rkey) r,

(SELECT referencing_column_Name = c.name, Referencing_Object_name = o.name, f.constid FROM sysforeignkeys f, sysobjects o, syscolumns c WHERE (f.fkeyid = o.id) AND c.id = o.id AND c.colid = f.fkey) f

WHERE r.Referenced_Column_Name = f.referencing_column_Name AND r.constid = f.constid

ORDER BY f.Referencing_Object_name

Advertisements
Posted in 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