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