Script to find the PK and FK relation ship for the table


Hi All,

Today I wrote the script to find the primary key and foreign key for the table and Below is the script

select distinct sc.name+’.’+so.name as TableName,so2.name as ReferencedTable,so1.name ‘primary key Constraint name’,fc.CONSTRAINT_NAME as ‘Foreign Key constraint name’,col.COLUMN_NAME as ‘Primary Key Column’,

co2.COLUMN_NAME as ‘foreign key column’

from sys.objects so with (nolock)

inner join

sys.objects so1 (nolock)

inner join

sys.schemas sc(nolock)

on so1.schema_id=sc.schema_id

on so.[object_id]=so1.parent_object_id

and so.[type]=’u’

and so1.[type]=’PK’

inner join

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fc (nolock)

on fc.UNIQUE_CONSTRAINT_NAME=so1.name

inner join

sys.foreign_keys sfo (nolock)

on sfo.name=fc.CONSTRAINT_NAME

inner join

sys.objects so2(nolock)

on so2.object_id=sfo.parent_object_id

inner join

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE col(nolock)

on

col.CONSTRAINT_NAME=fc.UNIQUE_CONSTRAINT_NAME

inner join

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE co2 (nolock)

on co2.CONSTRAINT_NAME=fc.CONSTRAINT_NAME

Result:

Please check and suggest the best way for doing this

Thanks for viewing.

Regards,

Chaitanya,

Webiste : https://mssqlbuzz.wordpress.com/

To Send Mail : mssqlbuzz

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: