Locate primary key - foreign key relations

  • Mohit Nayyar

    Ten Centuries

    Points: 1008

    Comments posted to this topic are about the item Locate primary key - foreign key relations

    Thanks
    Mohit Nayyar
    http://mohitnayyar.blogspot.com/
    "If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

  • Todd Sherman-487999

    SSC Veteran

    Points: 221

    Howdy, I expanded this a bit, and its now my favorite query for a new database 🙂

    By adding the bit to the WHERE clause, it brings back information on all the key fields so its a little more generic and useful for a first glance.

    SELECT base.TABLE_NAME, base.COLUMN_NAME, cons.CONSTRAINT_TYPE

    FROM INFORMATION_SCHEMA.COLUMNS base

    LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE usage ON

    (base.TABLE_SCHEMA = usage.TABLE_SCHEMA

    and base.TABLE_NAME = usage.TABLE_NAME

    and base.COLUMN_NAME = usage.COLUMN_NAME)

    LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS cons on

    (base.TABLE_SCHEMA = usage.TABLE_SCHEMA

    and usage.CONSTRAINT_NAME = cons.CONSTRAINT_NAME

    and usage.TABLE_NAME = cons.TABLE_NAME)

    WHERE base.COLUMN_NAME in

    (SELECT COLUMN_NAME

    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

    WHERE CONSTRAINT_NAME LIKE '%_pk')

    ORDER BY 1, 2

  • Prasad Bhogadi

    SSCrazy Eights

    Points: 9235

    Todd Sherman (11/7/2007)


    Howdy, I expanded this a bit, and its now my favorite query for a new database 🙂

    By adding the bit to the WHERE clause, it brings back information on all the key fields so its a little more generic and useful for a first glance.

    SELECT base.TABLE_NAME, base.COLUMN_NAME, cons.CONSTRAINT_TYPE

    FROM INFORMATION_SCHEMA.COLUMNS base

    LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE usage ON

    (base.TABLE_SCHEMA = usage.TABLE_SCHEMA

    and base.TABLE_NAME = usage.TABLE_NAME

    and base.COLUMN_NAME = usage.COLUMN_NAME)

    LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS cons on

    (base.TABLE_SCHEMA = usage.TABLE_SCHEMA

    and usage.CONSTRAINT_NAME = cons.CONSTRAINT_NAME

    and usage.TABLE_NAME = cons.TABLE_NAME)

    WHERE base.COLUMN_NAME in

    (SELECT COLUMN_NAME

    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

    WHERE CONSTRAINT_NAME LIKE '%_pk')

    ORDER BY 1, 2

    Probably if you want all the constraints in the database you may want to just use something like

    SELECT base.TABLE_NAME,

    base.COLUMN_NAME,

    cons.CONSTRAINT_TYPE

    FROM

    INFORMATION_SCHEMA.COLUMNS base

    LEFT JOIN

    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE usage ON (base.TABLE_SCHEMA = usage.TABLE_SCHEMA

    and

    base.TABLE_NAME = usage.TABLE_NAME

    and

    base.COLUMN_NAME = usage.COLUMN_NAME)

    LEFT JOIN

    INFORMATION_SCHEMA.TABLE_CONSTRAINTS cons on (base.TABLE_SCHEMA = usage.TABLE_SCHEMA and usage.CONSTRAINT_NAME = cons.CONSTRAINT_NAME

    and

    usage.TABLE_NAME = cons.TABLE_NAME)

    WHERE

    cons.CONSTRAINT_TYPE IS NOT NULL

    or if you want to check for only Primary key or foreign keys you may change your where clause to include the filter on cons.CONSTRAINT_TYPE = @Constraint_type

    Assuming you replace @Constraint_type with appropriate filter condition.

    Prasad Bhogadi
    www.inforaise.com

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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