Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Get Primary key – Foreign key relations table in sql server

Posted by Viral Sarvaiya on November 4, 2009


select * from information_schema.constraint_column_usage

This Query give the list of the table with the constraint


select * from information_schema.referential_constraints

this query give the list of the table with the foreign key constraint

now the query to find the relations


select

tblAll.table_name as Primary_TableName,

tblAll.column_name as Primary_TableColumn,

tblFK.table_name as ForeignKey_TableName,

tblFK.column_name as ForeignKey_ColumnName

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

Enjoy coding…..

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

 
%d bloggers like this: