SQL Server の参照整合性制約の状態を取得 を階層含めてとってみた

面白そうなネタがあったので書いてみた。
元ネタ:SQL Server の参照整合性制約の状態を取得 | SE の雑記
DBは、SQL Server 2012 の ReportServer のデータベースを使ってます。

with [base_table] as ( 
  select distinct
    t.name as [table_name]
    , OBJECT_NAME(fkc.referenced_object_id) [referenced_object_name] 
  from 
    sys.tables t left join sys.foreign_keys fk on (
      t.object_id = fk.parent_object_id
    ) left join sys.foreign_key_columns fkc on (
      fk.object_id = fkc.constraint_object_id 
    )
  where 
    t.object_id <> fkc.referenced_object_id 
    or fkc.referenced_object_id is null
), [foreign_key_table] as (
  select 
    [table_name] as [main_table_name]
    , [table_name]
    , [referenced_object_name]
    , cast([table_name] as nvarchar(4000)) as [referenced_order]
  from 
    [base_table]
  union all 
  select 
    [foreign_key_table].[main_table_name]
    , [base_table].[table_name]
    , [base_table].[referenced_object_name]
    , [foreign_key_table].[referenced_order] + N' -> ' + [base_table].[table_name]
  from
    [base_table] inner join [foreign_key_table] on ( 
      [base_table].[table_name] = [foreign_key_table].[referenced_object_name]
    )
)

select 
  [main_table_name], [referenced_order]  
from 
  [foreign_key_table] 
where 
  [referenced_object_name] is  null
  and [main_table_name] <> [referenced_order] -- この条件を外すと 外部キー が無いテーブルも出てくる
order by 
  [main_table_name]
-- 再帰の上限が足りない場合は、設定する option (maxrecursion 〜)

この DB の外部キーの構成は、

  • 自テーブルに結合する外部キー (1テーブルで親子関係を持つケース)
  • 1テーブルから、同じテーブルに対する複数の外部キー (登録者と更新者 をユーザーテーブルを見にいくケース)

といったパターンがありましたが、パッと見取れてる風なのであってるかな?
もうちょっと欲を言えば

ActiveSubscriptions -> Subscriptions -> Users

ActiveSubscriptions -> Subscriptions -> Catalog -> Users

があるので消したかったけど力尽きた。。