Find All Foreign Keys

Posted: April 12, 2011 in SQL Server 2005

This query returns foreign key relationships details for the database:

-- Select all foreign key relationships
SELECT   fk.table_name, 
         cu.column_name, 
         pk.table_name, 
         pt.column_name, 
         c.constraint_name 
FROM     information_schema.referential_constraints      c 
         INNER JOIN information_schema.table_constraints fk  ON fk.constraint_name = c.constraint_name
                                                            AND fk.table_name      = '<fk_table_name>'
         INNER JOIN information_schema.table_constraints pk  ON pk.constraint_name = c.unique_constraint_name
                                                            AND pk.table_name      = '<pk_table_name>'
         INNER JOIN information_schema.key_column_usage  cu  ON c.constraint_name  = cu.constraint_name 
         INNER JOIN (SELECT i1.table_name, 
                            i2.column_name 
                     FROM   information_schema.table_constraints           i1 
                            INNER JOIN information_schema.key_column_usage i2 ON i1.constraint_name = i2.constraint_name 
                     WHERE  i1.constraint_type = 'PRIMARY KEY') pt ON pt.table_name = pk.table_name 
WHERE    c.constraint_name LIKE '%<constraint_name>%'
ORDER BY fk.table_name;
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