Как найти первичные ключи для всех внешних ключей в БД, используя INFORMATION_SCHEMA

Question

Я хочу найти все внешние ключи в таблице, и для каждого внешнего ключа найти таблицу первичного ключа и столбец, на который она указывает. Мне нужно сделать это с помощью INFORMATION_SCHEMA, потому что мы хотим использовать это против всех поставщиков БД (или, по крайней мере, всех, кто правильно реализует INFORMATION_SCHEMA).

Лучшее, что я придумал, это:

"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')";

Это кажется мне слишком сложным. Есть ли лучший выбор для этого?

спасибо - Дэйв

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

Я проверил это на базе данных SQL Server 2008, и при запуске в контексте моей пользовательской базы данных он возвращал все мои определенные таблицы с внешними ключами, а также связанную таблицу и столбец.

Вы также можете отфильтровать это дальше, конечно.

Answer 2

Первоначально принятый ответ не учитывает должным образом базу данных с использованием составных первичных ключей.

Попробуйте это вместо этого:

select  fku.CONSTRAINT_NAME, fku.ORDINAL_POSITION
        , fku.TABLE_SCHEMA as FK_TABLE_SCHEMA
        , fku.TABLE_NAME as FK_TABLE_NAME
        , fku.COLUMN_NAME as FK_COLUMN_NAME
        , pku.TABLE_SCHEMA as PK_TABLE_SCHEMA
        , pku.TABLE_NAME as PK_TABLE_NAME
        , pku.COLUMN_NAME as PK_COLUMN_NAME

from    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pku
            ON rc.UNIQUE_CONSTRAINT_NAME = pku.CONSTRAINT_NAME
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fku
            ON rc.CONSTRAINT_NAME = fku.CONSTRAINT_NAME AND pku.ORDINAL_POSITION = fku.ORDINAL_POSITION
order by fku.TABLE_SCHEMA, fku.TABLE_NAME, fku.ORDINAL_POSITION

Answer 3

Ответ Джима не может правильно вернуть все внешние ключи, если у указанной таблицы (таблицы, против которой ищет внешний ключ) есть уникальный ключ, потому что он использует столбец UNIQUE_CONSTRAINT_NAME. Я предлагаю использовать:

SELECT 
    FK = fk.name, 
    FKTable = QUOTENAME(OBJECT_SCHEMA_NAME(fkcol.[object_id])) 
        + '.' + QUOTENAME(OBJECT_NAME(fkcol.[object_id])),
    FKCol = fkcol.name,
    ' references => ',
    PKTable = QUOTENAME(OBJECT_SCHEMA_NAME(pkcol.[object_id])) 
        + '.' + QUOTENAME(OBJECT_NAME(pkcol.[object_id])),
    PKCol = pkcol.name
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc
ON fk.[object_id] = fkc.constraint_object_id
INNER JOIN sys.columns AS fkcol
ON fkc.parent_object_id = fkcol.[object_id]
AND fkc.parent_column_id = fkcol.column_id
INNER JOIN sys.columns AS pkcol
ON fkc.referenced_object_id = pkcol.[object_id]
AND fkc.referenced_column_id = pkcol.column_id
ORDER BY fkc.constraint_column_id;

Источник:

• Дело против просмотров INFORMATION_SCHEMA