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

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?

Answer 1

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]';

Answer 2

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