外部キー制約の発見

SQL Serverのヒントとトリック

これは、私は私は他の人に役立つことができると思います私は年間を通じて蓄積している簡単なヒントやトリックのシリーズの一部です.
場合は、同様の短いヒントやトリックをコメントを残してください.

すべての外部キー制約

よく設計されたデータベースは、データ完全性を保証するために、外国のキーリレーションを使用して、そのテーブルを互いに関連づけます.しかし、データベースが何百または何千ものテーブルを持っている巨大な課題を変更する必要があります特定のテーブルに関連するすべてのテーブルを見つけることができます.
以下のクエリは、特定の列名を持つ列間のすべての外部キー制約を検索します.

DECLARE @colname VARCHAR(200) = 'customer'

SELECT fk_tab.name as [table],    
       pk_columns.name as colname,
       pk_tab.name as [fk_table],
       fk_columns.name as fk_colname
  FROM sys.foreign_keys fk
  JOIN sys.tables fk_tab on fk_tab.object_id = fk.parent_object_id
  JOIN sys.tables pk_tab on pk_tab.object_id = fk.referenced_object_id
  JOIN sys.foreign_key_columns fk_cols on fk_cols.constraint_object_id = fk.object_id
  JOIN sys.columns fk_columns ON fk_columns.object_id = fk_cols.referenced_object_id 
       AND fk_columns.column_id = fk_cols.referenced_column_id
  JOIN sys.columns pk_columns ON pk_columns.object_id = fk_cols.parent_object_id
       AND pk_columns.column_id = fk_cols.parent_column_id
 WHERE fk_columns.name LIKE '%'+ @colname +'%' OR pk_columns.name LIKE '%'+ @colname +'%'
 ORDER BY pk_tab.name

以下のNorthwindの例では、CustomerCustomerEdテーブルのCustomerTypeId列に、表CustomerDemographicsの主キー列CustomerTypeIDに外部キーの関係があることがわかります.