March 18, 2014 at 9:13 am
Hi there...
I like to know is there a way to write a query in which i can find out All the PRIMARY KEY Relationship with Foreign key and on which table is foreign key is located to the particular primary key ...
March 18, 2014 at 9:45 am
Have a look at sys.key_constraints, sys.foreign_keys, and sys.foreign_key_column
March 18, 2014 at 9:46 am
This should get you what you want - I can't remember the exact post but this code comes from stackoverflow or somewhere similar SELECT 'PK',
kc.name,
c.NAME
FROM
sys.key_constraints kc
INNER JOIN
sys.index_columns ic ON kc.parent_object_id = ic.object_id
INNER JOIN
sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE
kc.type = 'PK'
SELECT 'FK',
OBJECT_NAME(parent_object_id) 'Parent table',
c.NAME 'Parent column name',
OBJECT_NAME(referenced_object_id) 'Referenced table',
cref.NAME 'Referenced column name'
FROM
sys.foreign_key_columns fkc
INNER JOIN
sys.columns c
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.object_id
INNER JOIN
sys.columns cref
ON fkc.referenced_column_id = cref.column_id
AND fkc.referenced_object_id = cref.object_id
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply