Delete Rows Containing a GUID

Sometimes you need to remove every trace of a GUID from an entire database - for example, scrubbing a record that is referenced by uniqueidentifier columns all over the place. This stored procedure scans every user table in the target database for uniqueidentifier columns and deletes any row containing the given GUID, printing how many rows were removed from each column it touched.

Example: EXEC DeleteGUIDRows @DB = 'MyDatabase', @GUID = '00000000-0000-0000-0000-000000000000'

-- =============================================
-- Author:		Josh Patterson
-- Create date: 4/7/2006
-- Description:	This SP will search all UniqueIdentifier columns in the entire
--					database specified. It will (((**DELETE**))) any rows found to contain the GUID.
-- =============================================
CREATE PROCEDURE DeleteGUIDRows
(
	@DB		VarChar(100),
	@GUID	UniqueIdentifier
)
AS
BEGIN
	SET NOCOUNT ON

	IF Exists (SELECT * FROM [Master]..SysObjects WHERE Type = 'U' AND Name = 'xTemp_Schema')
	BEGIN
		DROP TABLE [Master]..xTemp_Schema
	END

	DECLARE @TableName	VarChar(255)
	DECLARE @ColumnName	VarChar(255)
	DECLARE @sSQL		VarChar(MAX)

	SET @sSQL = '
		SELECT
			Objects.Name as [Table],
			Columns.Name as [Column]
		INTO
			[Master]..xTemp_Schema
		FROM
			[' + @DB + '].Sys.Columns as [Columns],
			[' + @DB + '].Sys.Objects as [Objects],
			[' + @DB + '].Sys.Types as [Types]
		WHERE
			Columns.Object_Id = Objects.Object_Id
			AND Columns.User_Type_Id = Types.User_Type_Id
			AND Objects.Type = ''U''
			AND Types.Name = ''UniqueIdentifier''
			Order By
			Objects.Name,
			Columns.Name'

	EXEC(@sSQL)

	--Declare the SQL cursor.
	DECLARE DeleteGUIDRows CURSOR FOR
		SELECT
			[Table],
			[Column]
		FROM
			[Master]..xTemp_Schema
		ORDER BY
			[Table],
			[Column]
		
	OPEN DeleteGUIDRows --Open SQL the cursor.

	--Push the first rows data into the variables.
	FETCH NEXT
	FROM DeleteGUIDRows
	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), @GUID) + ''');
			IF @NumOfMatches > 0 BEGIN
				DELETE FROM [' + @DB + ']..[' + @TableName + ']
					WHERE [' + @ColumnName + '] = ''' + Convert(VarChar(36), @GUID) + '''

				PRINT ''[' + @DB +  '].[' + @TableName + '].[' + @ColumnName + ']: '' + Convert(VarChar(10), IsNull(@NumOfMatches, 0)) + '' row(s)''
			END;'

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

	--Close the cursor and do any cleaning up.
	CLOSE DeleteGUIDRows DEALLOCATE DeleteGUIDRows

	EXEC('DROP TABLE [Master]..xTemp_Schema')
END
GO