Created
August 6, 2014 20:53
-
-
Save chenrui333/e07d93dff29b11f5320a to your computer and use it in GitHub Desktop.
Find the Foreign Key Associated with a Given Primary Key
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
o1.name AS FK_table, | |
c1.name AS FK_column, | |
fk.name AS FK_name, | |
o2.name AS PK_table, | |
c2.name AS PK_column, | |
pk.name AS PK_name, | |
fk.delete_referential_action_desc AS Delete_Action, | |
fk.update_referential_action_desc AS Update_Action | |
FROM sys.objects o1 | |
INNER JOIN sys.foreign_keys fk | |
ON o1.object_id = fk.parent_object_id | |
INNER JOIN sys.foreign_key_columns fkc | |
ON fk.object_id = fkc.constraint_object_id | |
INNER JOIN sys.columns c1 | |
ON fkc.parent_object_id = c1.object_id | |
AND fkc.parent_column_id = c1.column_id | |
INNER JOIN sys.columns c2 | |
ON fkc.referenced_object_id = c2.object_id | |
AND fkc.referenced_column_id = c2.column_id | |
INNER JOIN sys.objects o2 | |
ON fk.referenced_object_id = o2.object_id | |
INNER JOIN sys.key_constraints pk | |
ON fk.referenced_object_id = pk.parent_object_id | |
AND fk.key_index_id = pk.unique_index_id | |
ORDER BY o1.name, o2.name, fkc.constraint_column_id |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Reference link:
http://dba.stackexchange.com/questions/31720/how-to-find-the-foreign-key-associated-with-a-given-primary-key