読者です 読者をやめる 読者になる 読者になる

SQL Server - 外部キーの一覧を取得するクエリ

sql-server t-sql

テーブル名やカラム名を含めて外部キーの一覧を取得するクエリです。探したら似たようなクエリが見つかる気もしますが練習もかねて書いてみました。また使うことがあるかなと。

select
    fk.name as [外部キー名],
    fkc.constraint_column_id as [外部キーカラムID],
    pt.name as [参照元テーブル名],
    pc.name as [参照元カラム名],
    rt.name as [参照先テーブル名],
    rc.name as [参照先カラム名]
from sys.foreign_keys as fk
    inner join sys.foreign_key_columns as fkc
        on fk.object_id = fkc.constraint_object_id
    -- 参照元のテーブルとカラムをjoin
    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
    -- 参照先のテーブルとカラムをjoin
    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 [外部キー名], [外部キーカラムID];

適当なサンプルテーブルを作ってクエリを実行した結果はこんな感じです。

-- Table2がTable1を参照している
create table Table1(
    Id int not null,
    constraint PK_Table1 primary key(Id)
);
create table Table2(
    Id int not null,
    Table1Id int not null,
    constraint FK_Table2_Table1 foreign key(Table1Id) references Table1(Id));

-- 結果
/*
外部キー名        外部キーカラムID 参照元テーブル名  参照元カラム名 参照先テーブル名 参照先カラム名
---------------- -------------- --------------- ------------ -------------- ------------
FK_Table2_Table1 1              Table2          Table1Id     Table1         Id
*/

複合外部キーの場合。

-- Table4がTable3を複合外部キーで参照している
create table Table3(
    Id1 int not null,
    Id2 int not null,
    constraint PK_Table3 primary key(Id1, Id2)
);
create table Table4(
    Id int not null,
    Table3Id1 int not null,
    Table3Id2 int not null,
    constraint FK_Table4_Table3 foreign key(Table3Id1, Table3Id2) references Table3(Id1, Id2));

-- 結果
/*
外部キー名        外部キーカラムID 参照元テーブル名  参照元カラム名 参照先テーブル名 参照先カラム名
---------------- -------------- --------------- ------------ -------------- ------------
FK_Table4_Table3 1              Table4          Table3Id1    Table3         Id1
FK_Table4_Table3 2              Table4          Table3Id2    Table3         Id2
*/

参考