Guid Search

Where is this GUID hiding? This stored procedure scans every uniqueidentifier column in the current database and prints each table.column that contains the given GUID, along with a row count. It is the read-only companion to the DeleteGUIDRows procedure - run this first to see what you would be touching.

Example: EXEC GuidSearch @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 then print any results found. Happy hunting!
-- =============================================
ALTER PROCEDURE GuidSearch
	@DB		nVarChar(1024),
	@GUID	UniqueIdentifier
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @TableName	nVarChar(256)
	DECLARE @SchemaName	nVarChar(256)
	DECLARE @ColumnName	nVarChar(256)
	DECLARE @sSQL		nVarChar(MAX)

	DECLARE GUIDSearch CURSOR FAST_FORWARD FOR
		SELECT
			schema_name(SysO.schema_id),
			SysO.name,
			SysC.name
		FROM
			sys.columns as SysC
		INNER JOIN sys.objects as SysO
			ON SysO.object_id = SysC.object_id
		INNER JOIN sys.types as SysT
			ON SysT.user_type_id = SysC.user_type_id
		WHERE
			SysT.name = 'uniqueidentifier'
		ORDER BY
			schema_name(SysO.schema_id),
			SysO.name,
			SysC.name
	OPEN GUIDSearch

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

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

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

		Exec(@sSQL)
		
		------------------------------------------------------------------------------------------------
		FETCH FROM GUIDSearch INTO @SchemaName, @TableName, @ColumnName
	END

	CLOSE GUIDSearch DEALLOCATE GUIDSearch
END
GO