Replace a GUID Everywhere

The update companion to GuidSearch: this stored procedure replaces every occurrence of one GUID with another across all uniqueidentifier columns in a database, printing how many records were affected in each column. It relies on the GetTableColumns procedure to enumerate the candidate columns.

Example: EXEC ReplaceGUID @DB = 'MyDatabase', @OldGUID = '11111111-1111-1111-1111-111111111111', @NewGUID = '22222222-2222-2222-2222-222222222222'

-- =============================================
-- Author:		Josh Patterson
-- Create date: 4/18/2006
-- Description:	This stored procedure is used to replace all occurrences of one GUID with another.
-- =============================================
CREATE PROCEDURE ReplaceGUID
	@DB	VarChar(100),
	@OldGUID UniqueIdentifier,
	@NewGUID UniqueIdentifier
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @TableName	VarChar(1024)
	DECLARE @ColumnName	VarChar(1024)
	DECLARE @sSQL		VarChar(MAX)
	DECLARE @TotalRowsAffected		Decimal
	DECLARE @TotalColumnsAffected	Decimal

	DECLARE @Struct TABLE
	(
		[Table] VarChar(1024),
		[Column] VarChar(1024)
	)

	INSERT INTO @Struct EXEC GetTableColumns @DB, 'UniqueIdentifier'

	--Declare the SQL cursor.
	DECLARE SQLImport CURSOR FOR

		SELECT
			[Table],
			[Column]
		FROM
			@Struct
		ORDER BY
			[Table],
			[Column]

	OPEN SQLImport --Open SQL the cursor.

	--Push the first rows data into the variables.
	FETCH NEXT
	FROM SQLImport
	INTO
		@TableName,
		@ColumnName

	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		------------------------------------------------------(Begin Loop)--------------------------

		Set @sSQL = ' DECLARE @NumOfMatches Decimal;
            Set @NumOfMatches = (SELECT COUNT(*) FROM [' + @DB + ']..[' + @TableName + '] as [' + @TableName + ']
            WHERE [' + @TableName + '].[' + @ColumnName + '] = ''' + Convert(VarChar(36), @OldGUID) + ''');
            IF @NumOfMatches > 0 BEGIN
            PRINT ''[' + @TableName + '].[' + @ColumnName + ']: '' + Convert(VarChar(10), IsNull(@NumOfMatches, 0)) + '' Records''
            END;'
		Exec(@sSQL)

		Set @sSQL = '
            UPDATE [' + @DB + ']..[' + @TableName + ']
            Set [' + @ColumnName + '] = ''' + Convert(VarChar(36), @NewGUID) + '''
            WHERE [' + @ColumnName + '] = ''' + Convert(VarChar(36), @OldGUID) + ''''
		Exec(@sSQL)

		------------------------------------------------------(End Loop)--------------------------
		--Push the next rows data into the variables.
		FETCH NEXT
		FROM SQLImport
		INTO
			@TableName,
			@ColumnName
	END

	--Close the cursor and do any cleaning up.
	CLOSE SQLImport DEALLOCATE SQLImport
END
GO