How to find primary keys for all foreign keys in a DB using INFORMATION_SCHEMA

Question

I want to find all foreign keys in a table, and for each foreign key find the primary key table & column it points to. I need to do this using INFORMATION_SCHEMA because we want to use this against all DB vendors (or at least all that properly implement INFORMATION_SCHEMA).

The best I have come up with is:

"SELECT k.COLUMN_NAME, ccu.TABLE_NAME AS 'references_table', ccu.COLUMN_NAME AS 'references_field' " +
"FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k LEFT OUTER JOIN " +
"INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c ON k.TABLE_NAME = c.TABLE_NAME AND k.TABLE_SCHEMA = c.TABLE_SCHEMA AND " +
"k.TABLE_CATALOG = c.TABLE_CATALOG AND k.CONSTRAINT_CATALOG = c.CONSTRAINT_CATALOG AND " +
"k.CONSTRAINT_NAME = c.CONSTRAINT_NAME LEFT OUTER JOIN " +
"INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc ON rc.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA AND " +
"rc.CONSTRAINT_CATALOG = c.CONSTRAINT_CATALOG AND rc.CONSTRAINT_NAME = c.CONSTRAINT_NAME LEFT OUTER JOIN " +
"INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON rc.UNIQUE_CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA AND " +
"rc.UNIQUE_CONSTRAINT_CATALOG = ccu.CONSTRAINT_CATALOG AND rc.UNIQUE_CONSTRAINT_NAME = ccu.CONSTRAINT_NAME " +
"WHERE(k.CONSTRAINT_CATALOG = DB_NAME()) AND (k.TABLE_NAME = '" + table.Name + "') AND (c.CONSTRAINT_TYPE = 'FOREIGN KEY')";

This strikes me as over-complicated. Is there a better select for this?

thanks - dave

Answer 1

SELECT  ccu1.TABLE_NAME as fkTable, ccu1.COLUMN_NAME as fkColumn, 
        ccu2.TABLE_NAME as referencedTable, ccu2.COLUMN_NAME as referencedColumn
FROM    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu1,
        INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2,
        INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
WHERE   rc.CONSTRAINT_NAME = ccu1.CONSTRAINT_NAME
AND     rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME

I tested this against a SQL Server 2008 database and when run under context of my user database, it returned all my defined tables with foreign keys, and the related table and column.

You can also filter this down further of course.