Generate Foreign Keys Drop and Create Statements of 1 and Many tables

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,
'Alter table ' + FK.TABLE_NAME + ' Drop Constraint ' + C.CONSTRAINT_NAME AS DropStatement,
'Alter table ' + FK.TABLE_NAME + ' Add Constraint ' + C.CONSTRAINT_NAME +' FOREIGN KEY(' +CU.COLUMN_NAME+') REFERENCES '+PK.TABLE_NAME+'('+PT.COLUMN_NAME+')' AS CreateStatement


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
WHERE PK.TABLE_NAME='Student'


Comments

Popular posts from this blog

TSQL To Get All the Names of tables and Databases whose data have changed in last 24 Hours

To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases

Apply paging in procedure