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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy