Find all foreign keys constraints in database referencing a certain primary key

Question

I want to find all foreign keys in my database that reference to a primary key of a certain table.

For example, I have a column A in table T which is the primary key. Now I want to find in which tables column A is referenced in a foreign key constraint?

One simple way I've considered is to check the database diagram, but this only works if a database is very small. It's not a very good solution for a database that has more than 50 tables.

Any alternatives?

or this one: http://stackoverflow.com/questions/1700303/sql-2008-foreign-key-constraints-in-the-information-schema-view (accepted answer covers the problem here I think) — Jon Egerton, Jan 07 '14 at 10:21

score 10 · Answer 1 · edited Feb 13 '14 at 22:02

On the last line, change [Primary Key Table] to your table name, change [Primary Key Column] to your column name, and execute this script on your database to get the foreign keys for the primary key.

SELECT FK.TABLE_NAME as Key_Table,CU.COLUMN_NAME as Foreignkey_Column,
       PK.TABLE_NAME as Primarykey_Table,
       PT.COLUMN_NAME as Primarykey_Column,
      C.CONSTRAINT_NAME as Constraint_Name 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME =Fk.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME=PK.CONSTRAINT_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 PK.TABLE_NAME = '[Primary Key Table]' and PT.COLUMN_NAME = '[Primary Key Column]';

score 1 · Accepted Answer · edited May 23 '17 at 12:01

1

Look at How to find foreign key dependencies in SQL Server?

You can sort on PK_Table and PK_Column to get what you want

edited May 23 '17 at 12:01

Community

1
1

answered Jan 07 '14 at 10:23

Thomas B. Lze

1,070
7
14

1

Thanks for your answer,and i find other way :right click on a table and select view dependencies . – Hamid Talebi Jan 07 '14 at 11:14

score 1 · Answer 3 · answered Jan 07 '14 at 10:36

You want to query the sys tables.

The query to get all table names where your column is used as a fk would be something like

 select name 
   from sys.tables 
  where object_id in 
       ( select parent_object_id 
           from sys.foreign_key_columns 
          where referenced_object_id = 12345
            and referenced_column_id = 1);

To get your referenced_object_id and referenced_column id:

select object_id from sys.tables where name = 'Table T'

With that object_id, find column id:

select column_id from sys.columns where name = 'Column A' and object_id = 12345

Find all foreign keys constraints in database referencing a certain primary key

3 Answers3