Skip to content

Instantly share code, notes, and snippets.

@beerose

beerose/fk.sql

Created March 17, 2021 11:41
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save beerose/ef1762e4b2a2fa56fbc2b027425b783e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save beerose/ef1762e4b2a2fa56fbc2b027425b783e to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Aleksandra Sikora created this gist Mar 17, 2021.
    31 changes: 31 additions & 0 deletions fk.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,31 @@
    SELECT
    fk.name AS constraint_name,
    sch1.name AS [table_schema],
    tab1.name AS [table_name],
    sch2.name AS [ref_table_schema],
    tab2.name AS [ref_table],
    (
    SELECT
    col1.name AS [column],
    col2.name AS [referenced_column]
    FROM sys.foreign_key_columns fkc
    INNER JOIN sys.columns col1
    ON col1.column_id = fkc.parent_column_id AND col1.object_id = tab1.object_id
    INNER JOIN sys.columns col2
    ON col2.column_id = fkc.referenced_column_id AND col2.object_id = tab2.object_id
    WHERE fk.object_id = fkc.constraint_object_id
    FOR JSON PATH
    ) AS column_mapping,
    fk.delete_referential_action_desc AS [on_delete],
    fk.update_referential_action_desc AS [on_update]
    FROM sys.foreign_keys fk
    INNER JOIN sys.objects obj
    ON obj.object_id = fk.referenced_object_id
    INNER JOIN sys.tables tab1
    ON tab1.object_id = fk.parent_object_id
    INNER JOIN sys.schemas sch1
    ON tab1.schema_id = sch1.schema_id
    INNER JOIN sys.tables tab2
    ON tab2.object_id = fk.referenced_object_id
    INNER JOIN sys.schemas sch2
    ON tab2.schema_id = sch2.schema_id for json path;