Technical Article

Script to Generate Foreign Keys

,

This Script will Generate a Script as a ResultSet, which need to be run to create Foreign Keys, or could be saved for future use.

This is a useful script to Regenerate Foreign Key Constraint , if its been deleted accidently or on purpose, and situation can be critical if those constaints name are been used within our Application code, 

When you run the script it will give you 2 ResultSets, First one is information about fetched Foreign Keys , and other one is Alter Script to add those foreign keys in corresponding tables. 

You can save that Second ResultSet to use as a whole or as a part to recover any Foreign Key.

There is a drawback in the script, i.e For Composite Foreign Keys, it generates create command twice, which cause an error, which needs to be fix manually. In this case you need to delete second command and modify first one with appropriate syntax.

See you next time guys....

SET NOCOUNT ON
DECLARE @temp TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyName NVARCHAR(200),
ForeignKeyTableSchema NVARCHAR(200),
ForeignKeyTableName NVARCHAR(200),
ForeignKeyColumnName NVARCHAR(200),
PrimaryKeyName NVARCHAR(200),
PrimaryKeyTableSchema NVARCHAR(200),
PrimaryKeyTableName NVARCHAR(200),
PrimaryKeyColumnName NVARCHAR(200)
)
INSERT INTO @temp(ForeignKeyName, ForeignKeyTableSchema, ForeignKeyTableName, ForeignKeyColumnName)
SELECT
CU.CONSTRAINT_NAME,
CU.TABLE_SCHEMA,
CU.TABLE_NAME,
CU.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON CU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE
TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @temp SET
PrimaryKeyName = UNIQUE_CONSTRAINT_NAME
FROM
@temp T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON T.ForeignKeyName = RC.CONSTRAINT_NAME
UPDATE @temp SET
PrimaryKeyTableSchema = TABLE_SCHEMA,
PrimaryKeyTableName = TABLE_NAME
FROM @temp T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON T.PrimaryKeyName = TC.CONSTRAINT_NAME
UPDATE @temp SET
PrimaryKeyColumnName = COLUMN_NAME
FROM @temp T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON T.PrimaryKeyName = KCU.CONSTRAINT_NAME
SELECT * FROM @temp

--ADDING CONSTRAINT SCRIPT:
SELECT
'ALTER TABLE ' + ForeignKeyTableSchema + '.' 
+ ForeignKeyTableName + 
' ADD CONSTRAINT ' + ForeignKeyName + 
' FOREIGN KEY(' + ForeignKeyColumnName + ') REFERENCES ' + PrimaryKeyTableSchema + '.' + PrimaryKeyTableName + 
'(' + PrimaryKeyColumnName + ') GO'
FROM
@temp
GO

Rate

3.8 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

3.8 (5)

You rated this post out of 5. Change rating