Technical Article

Update Foreign Key References

,

This script updates all foreign key references for the specified table. Useful if you need to delete a row. The follow example would update all uses of TableA.IDENTITYCOL where the value is 1 to the value of 2.

Example usage:

EXEC usp_updateFK 'TableA', 1, 2

CREATE PROCEDURE usp_updateFK (@TableName varchar(256), @OldValue int, @NewValue int) AS  
SET NOCOUNT ON
DECLARE @table varchar(256), @column varchar(256), @SQL nvarchar(4000)

DECLARE srcCur CURSOR FOR
SELECT DISTINCT
so1.name TableName, sc1.name ColumnName
FROM
sysforeignkeys sfk INNER JOIN sysobjects so1 ON (sfk.fkeyid = so1.id)
INNER JOIN sysobjects so2 ON (sfk.rkeyid = so2.id)
INNER JOIN syscolumns sc1 ON (sfk.fkey = sc1.colid and sc1.id = so1.id)
INNER JOIN syscolumns sc2 ON (sfk.rkey = sc2.colid and sc2.id = so2.id)
WHERE
so2.name = @TableName
FOR READ ONLY

OPEN srcCur
FETCH NEXT FROM srcCur INTO @table, @column

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'UPDATE ' + @table + ' SET ' + @column + ' = ' + CONVERT(nvarchar(32), @NewValue) + ' WHERE ' + @column + ' = ' + CONVERT(nvarchar(32), @OldValue)
EXEC sp_executesql @SQL

FETCH NEXT FROM srcCur INTO @table, @column
END

CLOSE srcCur
DEALLOCATE srcCur

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating