Foreign Key Map (sql query)

well quite a long break, have been bz with day to day schedule. a few days ago, i was looking for a query that will list all the tables ,with their forigen keys, with  refered primay key table and column, after looking into the system table , i crafted one myselft ,
here its

select ftable.name as FOREIGN_TABLE, fcol.name as FOREIGN_KEY, ptable.name as PRIMARY_TABLE,pcol.name as PRIMARY_KEY
from sysobjects as ftable ,sysforeignkeys fk ,syscolumns fcol ,sysobjects ptable,syscolumns pcol
where    ftable.id=fk.fkeyid
and    fk.fkey=fcol.colid
and     fcol.id=ftable.id
and     fk.rkeyid=ptable.id
and    pcol.id=ptable.id
and     fk.rkey=pcol.colid

sysobjects contains all objects in the database, all tables,all keys, all constrainst, etc,everything is kept here, and issued an id which is refered in other tables.
sysforeignkeys contains forgienKey constraint, and details about the relationn, column id etc,
syscolumns contains details about column of every table ,their names, their data type etc.
regards
faraz

Signature

Advertisements

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