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.


See Also