Find all foreign key references to a certain table

Question

I have a table that I would like to find all the other tables that foreign key it. I thought this thread had the answer: How can I list all foreign keys referencing a given table in SQL Server?

But after trying those things, it doesn't actually list all the tables.

Yes. It doesn't list all the tables that foreign key to this one. — , Oct 01 '12 at 13:36
Already answered here? http://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server-2005 — Blaise Swanwick, Oct 01 '12 at 13:37
I posted that exact link in my question and said it didn't work for me. — , Oct 01 '12 at 13:37
`Yes. It doesn't list all the tables that foreign key to this one` Can you triple check? — RichardTheKiwi, Oct 01 '12 at 13:43
It does not. That query listed 8 tables. The one below lists 12 tables. Basically if the other table's foreign key is also it's primary key, it doesn't include that in the results. — , Oct 01 '12 at 18:29

score 6 · Answer 1 · answered Oct 01 '12 at 13:41

Here you have another code snippet, at the end on the WHERE clause, substitute with your table name.

SELECT
    K_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.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 = 'your_table_name'

score 0 · Accepted Answer · answered Oct 01 '12 at 13:36

Try something like this:

;WITH ReferencingFK AS 
(
    SELECT 
        fk.Name AS 'FKName',
        OBJECT_NAME(fk.parent_object_id) 'ParentTable',
        cpa.name 'ParentColumnName',
        OBJECT_NAME(fk.referenced_object_id) 'ReferencedTable',
        cref.name 'ReferencedColumnName'
    FROM 
        sys.foreign_keys fk
    INNER JOIN 
        sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
    INNER JOIN 
        sys.columns cpa ON fkc.parent_object_id = cpa.object_id AND fkc.parent_column_id = cpa.column_id
    INNER JOIN 
        sys.columns cref ON fkc.referenced_object_id = cref.object_id AND fkc.referenced_column_id = cref.column_id
)
SELECT 
    FKName,
    ParentTable,
    ParentColumnName,
    ReferencedTable,
    ReferencedColumnName
FROM 
    ReferencingFK
WHERE
    ReferencedTable = '**YourTableNameHere**'
ORDER BY 
    ParentTable, ReferencedTable, FKName

That did it! Thanks! Wonder why sp_help doesn't list them all. — , Oct 01 '12 at 13:39

Find all foreign key references to a certain table

2 Answers2