Find List of All Foreign Keys in your Database
We all know that when we want to relate two different tables and obtain the information from both tables instead of just id's we use the concept of Foreign Keys. Here's a simple script that displays all your foreign keys in the database you are running it against.
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName,
'ALTER TABLE '+OBJECT_NAME(f.parent_object_id)+' ADD CONSTRAINT '+ f.name
+ ' FOREIGN KEY ('+COL_NAME(fc.parent_object_id, fc.parent_column_id)
+') REFERENCES '+ OBJECT_NAME(f.referenced_object_id) +'('
+ COL_NAME(fc.referenced_object_id, fc.referenced_column_id)+')' AS Syntax
FROM sys.foreign_keys f
INNER JOIN sys.foreign_key_columns fc ON f.OBJECT_ID = fc.constraint_object_id
Along with the code to view foreign keys, you can also see the syntax used to create the Foreign key for beginners (this is a concatenated field).
You can run this query against any database and it will list all of the Foreign keys present in the database, along with other attributes. In the image I have provided, I have chosen the database to be AdventureWorksDW.