SQL Server - 外部キーと参照先の一覧を取得する方法

SQL Server で外部キーと参照先の一覧を取得する方法

SQL Server で開発をしていて、マスター側の仕様変更などで、変更の影響を調べるのに、そのマスターデータを参照している外部キーの一覧を取得したいような状況ありませんか？

この記事では、SQL Server で外部キーと参照先の一覧を取得する方法をご紹介します。

システムカタログビューを使う方法

SQL Server では、sys.foreign_keys や sys.foreign_key_columns などのシステムカタログビューを使って、外部キーと参照先の一覧を取得することができます。

システムカタログビューを使って、外部キーと参照先の一覧を取得するクエリーは次の通りです。

SELECT  SCHEMA_NAME(PT.schema_id) AS FK_SchemaName, -- 外部キーを持つテーブルのスキーマ
        PT.name AS FK_TableName,                    -- 外部キーを持つテーブル
        FK.name AS FK_Name,                         -- 外部キー名
        PC.name AS FK_ColumnName,                   -- 外部キーを持つカラム
        SCHEMA_NAME(RT.schema_id) AS PK_SchemaName, -- 参照先テーブルのスキーマ
        RT.name AS PK_TableName,                    -- 参照先テーブル
        RC.name AS PK_ColumnName                    -- 参照先カラム
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.tables AS PT
                ON FKC.parent_object_id = PT.object_id
            INNER JOIN sys.columns AS PC
                ON FKC.parent_object_id = PC.object_id
                    AND FKC.parent_column_id = PC.column_id
            INNER JOIN sys.tables AS RT
                ON FKC.referenced_object_id = RT.object_id
            INNER JOIN sys.columns AS RC
                ON FKC.referenced_object_id = RC.object_id
                    AND FKC.referenced_column_id = RC.column_id
ORDER BY    FK_SchemaName,
            FK_TableName,          
            FK_Name,
            FK_ColumnName;

次のシステムカタログビューを結合して、外部キーを持つスキーマ・テーブル・カラムの情報と、参照先のスキーマ・テーブル・カラムの情報の一覧を取得しています。

sys.foreign_keys は外部キーの情報を返すシステムビューです。外部キー制約ごとに 1 行です。

sys.foreign_keys の name カラムが外部キー制約の名前です。

sys.foreign_key_columns は外部キーを構成する列の情報を返すシステムビューです。外部キーを構成する列または列のセットごとに 1 行です。

sys.foreign_key_columns の parent_object_id や parent_column_id が外部キーを持つテーブルやカラムの id です。

sys.foreign_key_columns の referenced_object_id や referenced_column_id が参照されるテーブルやカラムの id です。

sys.tables と sys.columns はテーブルとカラムの情報を返すシステムビューです。

このクエリーを AdventureWorks2022 データベースで実行してみると、次のように外部キーと参照先の一覧を取得できています。

[実行結果]

試しに、この外部キー制約を SSMS から確認してみます。

FK_Employee_Person_BusinessEntityID の外部キー制約の情報が正しく取得できていることがわかります。

参照先の Primary Key 側のテーブル名とカラム名を指定して一覧を取得したい時は WHERE 句で次のようにフィルターできます。

SELECT  SCHEMA_NAME(PT.schema_id) AS FK_SchemaName, -- 外部キーを持つテーブルのスキーマ
        PT.name AS FK_TableName,                    -- 外部キーを持つテーブル
        FK.name AS FK_Name,                         -- 外部キー名
        PC.name AS FK_ColumnName,                   -- 外部キーを持つカラム
        SCHEMA_NAME(RT.schema_id) AS PK_SchemaName, -- 参照先テーブルのスキーマ
        RT.name AS PK_TableName,                    -- 参照先テーブル
        RC.name AS PK_ColumnName                    -- 参照先カラム
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.tables AS PT
                ON FKC.parent_object_id = PT.object_id
            INNER JOIN sys.columns AS PC
                ON FKC.parent_object_id = PC.object_id
                    AND FKC.parent_column_id = PC.column_id
            INNER JOIN sys.tables AS RT
                ON FKC.referenced_object_id = RT.object_id
            INNER JOIN sys.columns AS RC
                ON FKC.referenced_object_id = RC.object_id
                    AND FKC.referenced_column_id = RC.column_id
WHERE       RT.name = 'Currency'
            AND RC.name = 'CurrencyCode'
ORDER BY    FK_SchemaName,
            FK_TableName,          
            FK_Name,
            FK_ColumnName;

RT.name が参照先のテーブル名、RC.name が参照先にカラム名です。

[実行結果]

Currency テーブルの CurrencyCode を参照している外部キー制約の一覧が取得できました。

システムストアドプロシージャを使う方法

SQL Server では、sp_fkeys というシステムストアドプロシージャを使っても、外部キーと参照先の一覧を取得することができます。

sp_fkeys は現在の環境の外部キーの情報を返すシステムストアドプロシージャで構文は次の通りです。

sp_fkeys
    [ [ @pktable_name = ] N'pktable_name' ]
    [ , [ @pktable_owner = ] N'pktable_owner' ]
    [ , [ @pktable_qualifier = ] N'pktable_qualifier' ]
    [ , [ @fktable_name = ] N'fktable_name' ]
    [ , [ @fktable_owner = ] N'fktable_owner' ]
    [ , [ @fktable_qualifier = ] N'fktable_qualifier' ]
[ ; ]

@pktable_name は、参照先のテーブル名です。

@pktable_owner は、参照先のテーブルのあるスキーマ名です。

@pktable_qualifier は、参照先のテーブルのあるデータベース名です。

@fktable_name は、外部キーを持つテーブル名です。

@fktable_owner は、外部キーを持つテーブルのあるスキーマ名です。

@fktable_qualifier は、外部キーを持つテーブルのあるデータベース名です。

引数には、少なくとも @pktable_name か @fktable_name を指定する必要があります。

選択されているデータベースの情報を返すのでよければ、*_qualifier は指定しなくて大丈夫です。

それでは、sp_fkeys を使って外部キーと参照先の一覧を取得してみます。

現在のデータベースの Sales スキーマの Currency テーブルを参照している外部キーの一覧を取得するには、次のようにできます。

EXEC sp_fkeys @pktable_name = 'Currency', @pktable_owner = 'Sales';

[実行結果]

先ほど同じ 3 つの外部キー制約の情報が取得できています。

また、現在のデータベースの Sales スキーマの SalesPerson テーブルの持っている外部キー制約の一覧を取得するには、次のようにできます。

EXEC sp_fkeys @fktable_name = 'SalesPerson', @fktable_owner = 'Sales';

[実行結果]

外部キー制約の名前でフィルターしたり、自由に検索できるので、システムカタログビューを使う方法のほうが便利かもしれませんね。

以上、SQL Server で外部キーと参照先の一覧を取得する方法をご紹介しました。