SQLServerでFKの一覧を取得したい

SQLServerでFKの一覧をずばっ！と取得するためには以下のようにINFORMATION_SCHEMAを用いる。

SELECT
    REF.CONSTRAINT_CATALOG AS [DB_NAME]
,    REF.CONSTRAINT_SCHEMA AS [SCHEMA]
,    REF.CONSTRAINT_NAME AS [FK_NAME]
,    COL_FK.TABLE_NAME AS [FK_TABLE_NAME]
,    COL_FK.COLUMN_NAME AS [FK_COLUMN_NAME]
,    COL_PK.TABLE_NAME AS [PK_TABLE_NAME]
,    COL_PK.COLUMN_NAME AS [PK_COLUMN_NAME]
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS REF
INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COL_FK
        ON (REF.CONSTRAINT_NAME = COL_FK.CONSTRAINT_NAME)
INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COL_PK
        ON (REF.UNIQUE_CONSTRAINT_NAME = COL_PK.CONSTRAINT_NAME)
ORDER BY
    COL_PK.TABLE_NAME
,    COL_PK.COLUMN_NAME

これで、FKの元と先を合わせて取得できちゃったりする。

べんりべんり

いじょ