Post reply

Script to Generate Foreign Keys

  • June 10, 2016 at 2:50 pm

    #327445

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

    Sheraz Mirza::hehe:

  • June 14, 2016 at 12:00 pm

    #1884987

    Very good and helpful!

  • July 5, 2016 at 10:04 am

    #1888506

    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.

  • July 29, 2016 at 7:15 am

    #1892632

    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 3 (of 3 total)

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