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

Share

Share

Rate