.net - 外部キー - sqlserver 依存 関係 更新



SQL Serverで外部キーの依存関係を見つける方法 (8)

特定の列の外部キーの依存関係をすべて見つけるにはどうすればよいですか?

さまざまな選択肢(SSMS、SQL Serverのクエリ/ビュー、サードパーティのデータベースツール、.NETのコード)は何ですか?

https://ffff65535.com


私はこのスクリプトが安価だと思う:

SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

@ "John Sansom"の答えのちょうどメモ、

外部キーの依存関係が求められている場合、私はPT Where句を次のようにする必要があります。

i1.CONSTRAINT_TYPE = 'FOREIGN KEY'  -- instead of 'PRIMARY KEY'

そのオン状態:

ON PT.TABLE_NAME = FK.TABLE_NAME – instead of PK.TABLE_NAME

通常、外来テーブルの主キーとして使用されているように、私はこの問題が以前は気づかれていなかったと思います。


USE information_schema;

SELECT COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM KEY_COLUMN_USAGE
WHERE (table_name = *tablename*) AND NOT (REFERENCED_TABLE_NAME IS NULL)

try: sp_help [table_name]

すべての外部キーを含むテーブルに関するすべての情報を取得します


このクエリは、テーブル内の外部キーの詳細を返します。複数の列キーをサポートします。

    SELECT *
    FROM
    (
    SELECT 
    T1.constraint_name ConstraintName,
    T2.COLUMN_NAME ColumnName,
    T3.TABLE_NAME RefTableName, 
    T3.COLUMN_NAME RefColumnName,
    T1.MATCH_OPTION MatchOption, 
    T1.UPDATE_RULE UpdateRule, 
    T1.DELETE_RULE DeleteRule
    FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS T1
    INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE T2 
    ON T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME
    INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE T3 
    ON T1.UNIQUE_CONSTRAINT_NAME = T3.CONSTRAINT_NAME 
    AND T2.ORDINAL_POSITION = T3.ORDINAL_POSITION) A
    WHERE A.ConstraintName = 'table_name'

テーブルや列の削除や名前の変更を計画している場合は、外部キーの依存関係のみを検出するだけでは不十分である可能性があります。

外部キーに接続されていないテーブルを参照する - 外部キーに接続されていない参照テーブルも検索する必要があります(外部キーは定義されていませんが、 )。 解決策は、すべてのテーブルで列名を検索し、同様の列を探すことです。

他のデータベースオブジェクト - これはおそらく少し話題ですが、すべての参照を探している場合は、依存オブジェクトをチェックすることも重要です。

GUIツール - SSMSの「関連オブジェクトの検索」オプションやApexSQL Search (フリーツール、SSMSへの統合)などのツールを使用して、外部キーに接続されたテーブルを含むすべての依存オブジェクトを識別します。


私が本当に使いたいのは、 Red Gate Softwareの SQL Dependency Tracker です 。 テーブルやストアドプロシージャなどの任意のデータベースオブジェクトを配置すると、選択したアイテムに依存する他のすべてのオブジェクト間の関係線が自動的に描画されます。

スキーマ内の依存関係を非常に適切にグラフィカルに表示します。


長い検索の後、私は実用的な解決策を見つけました。 私のデータベースはsys.foreign_key_columnsを使用せず、information_schema.key_column_usageは主キーのみを含みます。

私はSQL Server 2015を使用しています

解決策1(めったに使用されない)

他のソリューションがうまくいけない場合、これはうまく動作します:

        WITH CTE AS
        (
            SELECT 
                TAB.schema_id,
                TAB.name,
                COL.name AS COLNAME,
                COl.is_identity
            FROM 
                sys.tables TAB INNER JOIN sys.columns COL 
                    ON TAB.object_id = COL.object_id
        )
        SELECT 
            DB_NAME() AS [Database], 
            SCHEMA_NAME(Child.schema_id) AS 'Schema',
            Child.name AS 'ChildTable',
            Child.COLNAME AS 'ChildColumn',
            Parent.name AS 'ParentTable',
            Parent.COLNAME AS 'ParentColumn'
        FROM 
            cte Child INNER JOIN CTE Parent
                ON 
                    Child.COLNAME=Parent.COLNAME AND 
                    Child.name<>Parent.name AND 
                    Child.is_identity+1=Parent.is_identity

溶液2(一般に使用される)

ほとんどの場合、これはうまく動作します:

        SELECT
            DB_NAME() AS [Database], 
            SCHEMA_NAME(fk.schema_id) AS 'Schema',
            fk.name 'Name',
            tp.name 'ParentTable',
            cp.name 'ParentColumn',
            cp.column_id,
            tr.name 'ChildTable',
            cr.name 'ChildColumn',
            cr.column_id
        FROM
            sys.foreign_keys fk
        INNER JOIN
            sys.tables tp ON fk.parent_object_id = tp.object_id
        INNER JOIN
            sys.tables tr ON fk.referenced_object_id = tr.object_id
        INNER JOIN
            sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
        INNER JOIN
            sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
        INNER JOIN
            sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
        WHERE 
            -- CONCAT(SCHEMA_NAME(fk.schema_id), '.', tp.name, '.', cp.name) LIKE '%my_table_name%' OR
            -- CONCAT(SCHEMA_NAME(fk.schema_id), '.', tr.name, '.', cr.name) LIKE '%my_table_name%' 
        ORDER BY
            tp.name, cp.column_id




foreign-keys