wake-up-neo.com

Wie finde ich Fremdschlüsselabhängigkeiten in SQL Server?

Wie kann ich alle Fremdschlüsselabhängigkeiten für eine bestimmte Spalte finden?

Welche unterschiedlichen Alternativen gibt es (grafisch in SSMS, Abfragen/Ansichten in SQL Server, Datenbank-Tools von Drittanbietern, Code in .NET)?

.netsql-serverforeign-keys
161
Even Mien

Die folgende Abfrage soll Ihnen den Einstieg erleichtern. Es listet alle Fremdschlüsselbeziehungen in der aktuellen Datenbank auf.

SELECT
    FK_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
            SELECT
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME

Sie können Beziehungen auch grafisch in SQL Server Management Studio in Datenbankdiagrammen anzeigen.

284
John Sansom

versuchen: sp_help [table_name]

sie erhalten alle Informationen zur Tabelle, einschließlich aller Fremdschlüssel

99
Andrija

Wenn Sie vorhaben, eine Tabelle oder Spalte zu löschen oder umzubenennen, reicht es möglicherweise nicht aus, nur Fremdschlüsselabhängigkeiten zu finden.

Referenzieren von Tabellen, die nicht mit einem Fremdschlüssel verbunden sind - Sie müssen auch nach Referenziertabellen suchen, die möglicherweise nicht mit einem Fremdschlüssel verbunden sind (ich habe viele gesehen) Datenbanken mit schlechtem Design, für die keine Fremdschlüssel definiert wurden, für die jedoch verwandte Daten vorhanden waren). Die Lösung könnte darin bestehen, in allen Tabellen nach Spaltennamen zu suchen und nach ähnlichen Spalten zu suchen.

Andere Datenbankobjekte - Dies ist wahrscheinlich ein wenig falsches Thema, aber wenn Sie nach allen Referenzen gesucht haben, ist es auch wichtig, nach abhängigen Objekten zu suchen.

GUI-Tools - Probieren Sie die SSMS-Option „Verwandte Objekte suchen“ oder Tools wie ApexSQL Search (kostenloses Tool, integriert in SSMS) aus, um alle abhängigen Objekte einschließlich der mit Fremdschlüssel verbundenen Tabellen zu identifizieren.

39
Timothy Walden

Da Ihre Frage auf eine einzelne Tabelle ausgerichtet ist, können Sie Folgendes verwenden:

EXEC sp_fkeys 'TableName'

Ich fand es auf SO hier:

https://stackoverflow.com/a/12956348/652519

Ich fand die Informationen, die ich brauchte, ziemlich schnell. Es listet die Tabelle, die Spalte und den Namen des Fremdschlüssels auf.

[~ # ~] edit [~ # ~]

Hier ist ein Link zu der Dokumentation, in der die verschiedenen Parameter aufgeführt sind, die verwendet werden können: https://docs.Microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys- transact-sql

39
Michael

Ich denke, dieses Skript ist billiger:

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
28
Mimmo

Eines, das ich sehr gerne benutze, heißt SQL Dependency Tracker von Red Gate Software . Sie können beliebige Datenbankobjekte wie Tabellen, gespeicherte Prozeduren usw. einfügen. Anschließend werden automatisch die Beziehungslinien zwischen allen anderen Objekten gezeichnet, die von den ausgewählten Elementen abhängen.

Gibt eine sehr gute grafische Darstellung der Abhängigkeiten in Ihrem Schema.

6
TheTXI

Vielen Dank an John Sansom, seine Anfrage ist grandios!

Zusätzlich: Sie sollten am Ende Ihrer Abfrage "AND PT.ORDINAL_POSITION = CU.ORDINAL_POSITION" hinzufügen.

Wenn Sie mehrere Felder im Primärschlüssel haben, werden mit dieser Anweisung die entsprechenden Felder miteinander verglichen (ich hatte den Fall, Ihre Abfrage hat alle Kombinationen erstellt, also hatte ich für 2 Felder im Primärschlüssel 4 Ergebnisse für den entsprechenden Fremdschlüssel). .

(Entschuldigung, ich kann Johns Antwort nicht kommentieren, da ich nicht genug Rufpunkte habe.).

4
Sierramike

Diese Abfrage gibt Details zu Fremdschlüsseln in einer Tabelle zurück und unterstützt mehrere Spaltenschlüssel.

    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'
3
Kumait

Nach langer Suche fand ich eine funktionierende Lösung. Meine Datenbank verwendet nicht die sys.foreign_key_columns und das information_schema.key_column_usage enthält nur Primärschlüssel.

Ich benutze SQL Server 2015

LÖSUNG 1 (selten verwendet)

Wenn andere Lösungen nicht funktionieren, funktioniert dies problemlos:

        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

LÖSUNG 2 (häufig verwendet)

In den meisten Fällen funktioniert dies einwandfrei:

        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
2
profimedica

Sie können INFORMATION_SCHEMA.KEY_COLUMN_USAGE und sys.foreign_key_columns verwenden, um die Fremdschlüssel-Metadaten für eine Tabelle abzurufen, z. B. den Namen der Einschränkung, die Referenztabelle und die Referenzspalte usw.

Unten ist die Abfrage:

SELECT  CONSTRAINT_NAME, COLUMN_NAME, ParentTableName, RefTableName,RefColName FROM 
    (SELECT CONSTRAINT_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '<tableName>') constraint_details
    INNER JOIN  
    (SELECT ParentTableName, RefTableName,name ,COL_NAME(fc.referenced_object_id,fc.referenced_column_id) RefColName  FROM (SELECT object_name(parent_object_id) ParentTableName,object_name(referenced_object_id) RefTableName,name,OBJECT_ID  FROM sys.foreign_keys WHERE parent_object_id = object_id('<tableName>') ) f 
    INNER JOIN   
    sys.foreign_key_columns AS fc  ON  f.OBJECT_ID = fc.constraint_object_id ) foreign_key_detail 
    on foreign_key_detail.name = constraint_details.CONSTRAINT_NAME
2
Shreyansh Bothra

Nur eine Anmerkung für @ "John Sansom" Antwort,

Wenn die Fremdschlüssel Abhängigkeiten gesucht werden, denke ich, dass die PT Where-Klausel sein sollte:

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

und es ist die UNTER Bedingung:

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

Da üblicherweise der Primärschlüssel der Fremdtabelle verwendet wird, ist dieses Problem meines Erachtens bisher noch nicht aufgefallen.

1
Homo Programmatoris
SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

Es wird Ihnen geben:

Das FK selbst Schema, zu dem das FK gehört

  • Die "referenzierende Tabelle" oder die Tabelle mit der FK
  • Die "referenzierende Spalte" oder die Spalte in der referenzierenden Tabelle, die auf die FK verweist
  • Die "referenzierte Tabelle" oder die Tabelle mit der Schlüsselspalte, auf die Ihr FK zeigt
  • Die "referenzierte Spalte" oder die Spalte, auf die Ihr FK zeigt
0
Divya Agrawal