DDL Script to Generate Foreign Keys in SQL Server
Below is a script that will read all the?Foreign?Key references and create a SQL Server script to create and drop the constraints from the database.
--Create SELECT 'ALTER TABLE [' + SCHEMA_NAME(TB.schema_id) + '].[' + OBJECT_NAME(F.PARENT_OBJECT_ID)+ '] ADD CONSTRAINT [' + F.NAME + '] FOREIGN KEY '+'(['+COL_NAME(FC.PARENT_OBJECT_ID,FC.PARENT_COLUMN_ID)+ '])'+' REFERENCES [' + SCHEMA_NAME(FT.schema_id) + '].['+OBJECT_NAME (F.REFERENCED_OBJECT_ID)+'] ([' + COL_NAME(FC.REFERENCED_OBJECT_ID,FC.REFERENCED_COLUMN_ID)+'])' FROM SYS.FOREIGN_KEYS AS F JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_ID JOIN sys.tables AS TB ON F.parent_object_id = TB.object_id JOIN sys.tables AS FT ON F.REFERENCED_OBJECT_ID = FT.object_id ORDER BY SCHEMA_NAME(TB.schema_id), OBJECT_NAME(F.PARENT_OBJECT_ID) --Drop SELECT 'ALTER TABLE [' + SCHEMA_NAME(tb.schema_id) + '].[' + OBJECT_NAME(F.PARENT_OBJECT_ID)+ '] DROP CONSTRAINT [' + F.NAME + ']' FROM SYS.FOREIGN_KEYS AS F JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_ID JOIN sys.tables AS TB ON F.parent_object_id = TB.object_id ORDER BY SCHEMA_NAME(TB.schema_id), OBJECT_NAME(F.PARENT_OBJECT_ID)
Of course, this code is to be used at your own risk.