/ Published in: SQL
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
/*** Script all Foreign Key Constraints ***/ /*** The Result Set can be used to copy constraints to your testing DB or to keep on hand in case of errors. ***/ SELECT 'ALTER TABLE '+FK.TABLE_NAME+ ' ADD CONSTRAINT '+C.CONSTRAINT_NAME+' FOREIGN KEY'+ '('+CU.COLUMN_NAME+') '+ 'REFERENCES '+PK.TABLE_NAME+ '('+PT.COLUMN_NAME+')' ForeignKeyScripts 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