Find Foreign Keys

  • suba.sathyanathan 40131

    SSC Enthusiast

    Points: 191

    Comments posted to this topic are about the item Find Foreign Keys

  • dbasnyat25

    Newbie

    Points: 8

    soory i am getting an error

  • suba.sathyanathan 40131

    SSC Enthusiast

    Points: 191

    Not sure if there's any whitespace problem. Please use the below query.

    ;With CTE As

    (Select Object_Name(constraint_object_id) Constraint_Name,

    Object_Name(parent_object_id) Table_Name,

    C.name Column_Name

    From sys.foreign_key_columns FK

    Inner Join sys.columns C

    On FK.parent_object_id = C.object_id

    And FK.parent_column_id = C.column_id)

    Select C.Constraint_Name,

    C.Table_Name,

    C.Column_Name,

    Object_Name(FK.referenced_object_id) Referenced_Table_Name,

    SC.name Referenced_Column_Name

    from CTE C

    Inner Join sys.foreign_key_columns FK

    On C.Constraint_Name = Object_Name(FK.constraint_object_id)

    Inner Join sys.columns SC

    On FK.referenced_object_id = SC.object_id

    And FK.referenced_column_id = SC.column_id

  • suba.sathyanathan 40131

    SSC Enthusiast

    Points: 191

    Not sure if there's any whitespace problem. Please use the below query.

    ;With CTE As

    (Select Object_Name(constraint_object_id) Constraint_Name,

    Object_Name(parent_object_id) Table_Name,

    C.name Column_Name

    From sys.foreign_key_columns FK

    Inner Join sys.columns C

    On FK.parent_object_id = C.object_id

    And FK.parent_column_id = C.column_id)

    Select C.Constraint_Name,

    C.Table_Name,

    C.Column_Name,

    Object_Name(FK.referenced_object_id) Referenced_Table_Name,

    SC.name Referenced_Column_Name

    from CTE C

    Inner Join sys.foreign_key_columns FK

    On C.Constraint_Name = Object_Name(FK.constraint_object_id)

    Inner Join sys.columns SC

    On FK.referenced_object_id = SC.object_id

    And FK.referenced_column_id = SC.column_id

  • houming1982

    SSC Enthusiast

    Points: 110

    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

    ORDER BY

    FK_Column

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply