Script to Generate Foreign Keys

  • Sheraz.Mirza

    SSC Eights!

    Points: 805

    Comments posted to this topic are about the item Script to Generate Foreign Keys

    Sheraz Mirza::hehe:

  • brain.twizter

    Valued Member

    Points: 70

    Very good and helpful!

  • m.pradeep5259

    Valued Member

    Points: 71

    Hi Sheraz Mirza,

    This is a Useful script. But a little Correction to the Script. IF there are schema's used in the DB then the Query Gives some issues. If the same name exists in different Schema then its a problem so you should be inner join condition should check for Schema Compare between the two tables.

    SELECT

    CU.CONSTRAINT_NAME,

    CU.TABLE_SCHEMA,

    CU.TABLE_NAME,

    CU.COLUMN_NAME,

    TC.CONSTRAINT_TYPE

    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU

    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON CU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND CU.TABLE_SCHEMA = TC.CONSTRAINT_SCHEMA

    WHERE 1=1

    AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'

    AND CU.TABLE_SCHEMA in ('xyz','xyz123','xyz456')

    ORDER BY CU.TABLE_SCHEMA, CU.TABLE_NAME

    Hope This would would be helpful.

    Thanks once again and the query you gave is an informative and good one.

  • Sheraz.Mirza

    SSC Eights!

    Points: 805

    Thanks for your comments, you are right , actually i made script as per my environment and I believe your added lines will make it more generic and helpful for members.

    Sheraz Mirza::hehe:

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

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