TSQL – Select All Foreign Keys and their Associate Primary Keys

Thats what I was after !  Not so obvious in the INFORMATION.SCHEMA tables but this query will list all the foreign keys in the database along with their associated primary keys.


SELECT TC.CONSTRAINT_SCHEMA + '.'+ TC.TABLE_NAME AS PRIMARYKEYTABLE
,TC.CONSTRAINT_NAME AS PRIMARYKEY
,COALESCE(RC1.CONSTRAINT_NAME,'N/A') AS FOREIGNKEY
,CASE WHEN TC2.TABLE_NAME IS NULL THEN 'N/A'
 ELSE TC.CONSTRAINT_SCHEMA + '.' + TC2.TABLE_NAME END AS FOREIGNKEYTABLE
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
 LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC1 ON TC.CONSTRAINT_NAME =RC1.UNIQUE_CONSTRAINT_NAME
 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2 ON TC2.CONSTRAINT_NAME =RC1.CONSTRAINT_NAME
 WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
 ORDER BY TC.TABLE_NAME,TC.CONSTRAINT_NAME,RC1.CONSTRAINT_NAME

Thanks to …

http://wiki.lessthandot.com/index.php/Find_all_Primary_and_Foreign_Keys_In_A_Database

 

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

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