Find Relationship Between Foreign Keys and Primary Keys

Posted: February 14, 2011 in SQL Server 2005

The following is a quick script to indentify relationships between foreign and primary keys in the database.

SELECT f.name                                                    foreign_key_name,
       SCHEMA_NAME(f.SCHEMA_ID)                                  child_table_schema_name,
       OBJECT_NAME(f.parent_object_id)                           child_table_name,
       COL_NAME(fc.parent_object_id,fc.parent_column_id)         child_table_column_name,
       SCHEMA_NAME(o.SCHEMA_ID)                                  parent_table_schema_name,
       OBJECT_NAME (f.referenced_object_id)                      parent_table_name,
       COL_NAME(fc.referenced_object_id,fc.referenced_column_id) parent_table_column_name
FROM   SYS.FOREIGN_KEYS                   f
       INNER JOIN SYS.FOREIGN_KEY_COLUMNS fc ON f.OBJECT_ID = fc.constraint_object_id
       INNER JOIN SYS.OBJECTS             o  ON o.OBJECT_ID = fc.referenced_object_id
GO 
Advertisements

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