TSQLでテーブルの依存関係ツリーを出力する

外部キーが設定されたテーブルがあってdelete の順番が分からないとかデータ投入順番があるとか、そういう場合を考えて依存関係ツリーを出力するクエリーを作った

WITH 
table_dependencies as (
    select 
        dpendent_table.TABLE_SCHEMA as dpendent_table_schema,
        dpendent_table.TABLE_NAME as dpendent_table_name,
        principal_table.TABLE_SCHEMA as principal_table_schema,
        principal_table.TABLE_NAME as principal_table_name
    from 
        INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

        inner join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE as dpendent_table
            on REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA = dpendent_table.CONSTRAINT_SCHEMA
            and REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME = dpendent_table.CONSTRAINT_NAME

        inner join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE as principal_table
            on REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_SCHEMA = principal_table.CONSTRAINT_SCHEMA
            and REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME = principal_table.CONSTRAINT_NAME

      where
        principal_table.TABLE_SCHEMA <> dpendent_table.TABLE_SCHEMA
        OR principal_table.TABLE_NAME <> dpendent_table.TABLE_NAME
),
dependency_path  AS (
    select 
        TABLES.TABLE_SCHEMA as dpendent_table_schema,
        TABLES.TABLE_NAME as dpendent_table_name,
        cast(null as nvarchar(128)) as principal_table_schema,
        cast(null as nvarchar(128))  as principal_table_name,
        TABLES.TABLE_SCHEMA as root_table_schema,
        TABLES.TABLE_NAME as root_table_name,
        cast('/' + TABLES.TABLE_SCHEMA + '.' + TABLES.TABLE_NAME + '/' as nvarchar(512)) as dependency,
        1 as depth
    from 
        INFORMATION_SCHEMA.TABLES
    where
        TABLE_TYPE = 'BASE TABLE'

  UNION ALL

    select 
        table_dependencies.dpendent_table_SCHEMA as dpendent_table_schema,
        table_dependencies.dpendent_table_NAME as dpendent_table_name,
        table_dependencies.principal_table_schema as principal_table_schema,
        table_dependencies.principal_table_name as principal_table_name,
        dependency_path.root_table_schema as root_table_schema,
        dependency_path.root_table_name as root_table_name,
        cast(dependency_path.dependency + table_dependencies.dpendent_table_schema + '.' + table_dependencies.dpendent_table_name + '/' as nvarchar(512)) as dependency,
        depth + 1 as depth
    from
        table_dependencies

        INNER JOIN dependency_path
            ON table_dependencies.principal_table_name = dependency_path.dpendent_table_name
),
dependency_depth as(
    select
        dpendent_table_schema,
        dpendent_table_name,
        max(depth) as max_depth
    from
        dependency_path
    group by 
        dpendent_table_schema,
        dpendent_table_name
)

SELECT distinct
  dependency_path.root_table_schema,
  dependency_path.root_table_name,
  dependency_path.principal_table_schema,
  dependency_path.principal_table_name,
  dependency_path.dpendent_table_schema,
  dependency_path.dpendent_table_name,
  SUBSTRING(dependency_path.dependency, 2, LEN(dependency_path.dependency)-2) AS dependency,
  dependency_depth.max_depth
FROM
  dependency_path
  inner join dependency_depth
    on dependency_path.dpendent_table_schema = dependency_depth.dpendent_table_schema
    and dependency_path.dpendent_table_name = dependency_depth.dpendent_table_name
WHERE
    not exists(
        select * from dependency_path as duplicates
        where
            duplicates.DEPENDENCY <> dependency_path.DEPENDENCY
            and (duplicates.DEPENDENCY like '%' + dependency_path.DEPENDENCY or duplicates.DEPENDENCY like dependency_path.DEPENDENCY + '%' )
    )

order by
  dependency_depth.max_depth,
  dependency,
  dependency_path.root_table_schema,
  dependency_path.root_table_name,
  dependency_path.principal_table_schema,
  dependency_path.principal_table_name,
  dependency_path.dpendent_table_schema,
  dependency_path.dpendent_table_name

ここでは A-B-C. D-E の依存関係があるときに A-B-C, D-E を出力する。
A-B と B-C は対象外。

dependency_depth を order by max_depth asc とするとデータ投入時の順番、 order by max_depth desc とすると delete 時の順番が出力できる。