Get Primary key – Foreign key relations table in sql server 2005

Why it is required?

The main purpose of this is finding related constraint table from database.

Check below Query.

–get table list with constraint(primary and foreign key)

select * from information_schema.constraint_column_usage

–get table list with foreign key constraint

select * from information_schema.referential_constraints

–get the relation

select

tblAll.table_name as PrimaryTableName,

tblAll.column_name as PrimaryTableColumn,

tblFK.table_name as ForeignKeyTable,

tblFK.column_name as ForeignKeyColumn

from information_schema.constraint_column_usage tblAll

inner join information_schema.referential_constraints tblAllFK on tblAllFK.unique_constraint_name = tblAll.constraint_name

inner join information_schema.constraint_column_usage tblFK on tblAllFK.constraint_name=tblFK.constraint_name

Thnx

Advertisements

6 comments

  1. ALpesh Acharya · · Reply

    nice 1………i hv chk this query…

  2. Hi,

    Nice blog..publish you new content url at http://www.dotneturl.com and get reader and back link form us for free.

  3. thx patriwala,

    You realy save my time by using you queries you mentioned above.

    have great time.
    bye

  4. Thank u …..good one

  5. Thanks for you query

  6. Thanks a Lot.. Very useful blog.. :):)

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