Chris Straw
SHARE:

DDL Script to Generate Foreign Keys in SQL Server

Spread the love

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.

Written by

Chris Straw