/* Temporary table to hold all columns that have a GUID name or uniqueidentifier type.  
Holds an ID value (for looping), the table name and  the column type as well. */

DECLARE @TablesToBeSearched TABLE(RowId smallint IDENTITY(1,1)
					, TableName varchar(255)
					, ColName varchar(128)
					, ColType varchar(128))

--Temporary table to hold Table and Column names that are related to GUID        
DECLARE @TablesWithGuid TABLE(TableName varchar(255), ColName varchar(128), RowsFound int)       

DECLARE 	@TableName varchar(255)
, 		@ColName varchar(255)
, 		@GUID varchar(40)
, 		@ColType varchar(128)
, 		@SqlResult int
,		@strSql nvarchar(4000)
,		@currentrow smallint
,		@nextrow smallint
,		@loopcontrol smallint


--GUID that is being searched for.
SET @GUID = RTRIM(LTRIM('ENTER GUID HERE'))        

--Drop { and } characters if present. Return if invalid guid entered.
IF LEN(@GUID) = 38 AND CHARINDEX('{',@GUID,1) = 1 AND CHARINDEX('}',@GUID,1) = 38
BEGIN        
	SET @GUID = SUBSTRING(@GUID, 2, 36)        
END     
ELSE IF LEN(@GUID) <> 36
BEGIN
	SELECT 'Invalid Guid Specified' AS [ERROR]
	RETURN
END

-- Get meta data for columns and tables that are GUIDs
INSERT INTO @TablesToBeSearched
SELECT isc.[TABLE_NAME]
, 	isc.[COLUMN_NAME]
, 	isc.[DATA_TYPE]
FROM 	INFORMATION_SCHEMA.[Columns] isc INNER JOIN INFORMATION_SCHEMA.Tables ist 
	ON isc.[TABLE_NAME] = ist.[TABLE_NAME]
WHERE	((LOWER(isc.[Column_Name]) LIKE '%guid%' 
		AND isc.[Data_Type] IN ('char','varchar','nchar','nvarchar')
		AND COL_LENGTH((isc.[TABLE_NAME]),isc.[COLUMN_NAME]) > 35)
	OR isc.[Data_Type] = 'uniqueidentifier')
	AND ist.[TABLE_TYPE] = 'BASE TABLE'

--Initialize loop variables
SELECT	@loopcontrol = 1
SELECT	@nextrow = MIN(RowId)
FROM 	@TablesToBeSearched

--Make sure table has data
IF ISNULL(@nextrow,0) = 0
BEGIN
	SELECT 'No Guid columns found in the ' + DB_NAME() + ' database' AS [ERROR]
	RETURN
END

-- Retrieve the first row

SELECT	@currentrow = [RowId]
,	@TableName = [TableName]
,	@ColName = [ColName]
,	@ColType = [ColType]

FROM 	@TablesToBeSearched

WHERE 	RowId = @nextrow


WHILE @loopcontrol = 1        
BEGIN        
	/* Find which tables and columns have a matching Guid to the one we are searching for.  
	Return COUNT of matching rows as well. */      
	SET @SqlResult = 0

	--change guid string to have brackets for cases when the column is a varchar or nvarchar    
	IF @ColType IN ('char','varchar','nchar','nvarchar') 
	BEGIN        
		SET @GUID = '{' + @GUID + '}'        
	END        

	SET @strSql = 'SELECT @Result = COUNT(CAST([' + @ColName + '] AS varchar(255)))        
			FROM [' + @TableName + ']
			WHERE CAST([' + @ColName + '] AS varchar(255)) = ''' + @GUID + ''''  
		
		/* @ColName is CAST as a varchar type since the COUNT function will not work on 
		uniqueidentifier types.

		Since the @ColName is cast as a varchar, brackets {} cannot be used in the @GUID 
		string if the column is a uniqueidentifier.  Otherwise the uniqueidentifier rows will 
		not match up with the @GUID string. */

	EXECUTE sp_executesql @strSql, N'@Result int out', @SqlResult out  

	--If the result count is > 0 then add table, column, and count to @TablesWithGuid      
	IF @SqlResult > 0 
	BEGIN        
		INSERT INTO @TablesWithGuid VALUES (@TableName, @ColName, @SqlResult)        
	END        

	--reset guid string if column is not a uniqueidentifier
	IF @ColType IN ('char','varchar','nchar','nvarchar')  
	BEGIN         
		SET @GUID = SUBSTRING(@GUID, 2, 36)        
	END        

	-- Reset looping variables.            
            SELECT   @nextrow = NULL            

            -- get the next RowId
            SELECT   @nextrow = MIN(RowId)
            FROM     @TablesToBeSearched
            WHERE    [RowId] > @currentrow

            -- did we get a valid next row id?
            IF ISNULL(@nextrow,0) = 0
               BEGIN
                        BREAK
               END

            -- get the next row.
           SELECT	@currentrow = [RowId]
	,	@TableName = [TableName]
	,	@ColName = [ColName]
	,	@ColType = [ColType]
	FROM 	@TablesToBeSearched
	WHERE 	RowId = @nextrow     
END        
        
SELECT * FROM @TablesWithGuid ORDER BY TableName, ColName