--    query_text (parameter):  The query_text parameter is the search GUID value.
--    query: The query field provides the SQL statement for a given table column combination that can be used to see the details for the given rows.  
--      For example, if the row_count of the given query row is 5, the query will display those 5 rows. 
--    schema_name:  The schema_name provides the name of the schema of the table, it is often a dbo.
--    table_name: This indicates the name of the given table.
--    column_name:  This indicates the name of the given column.
--    row_count:  This provides the total number of rows for the given table where the GUID query_text occurs for the given column.

--    Notes for execution: first create the temporary procedure before running. 
--    It will be deleted when closing the connection. 



create procedure #sp_find_guid_in_tables @query_text nvarchar(255)
as
begin
    declare  @tables as table
    (
        schema_name     nvarchar(50),
        table_name         nvarchar(255),
        column_name     nvarchar(255),
        type_name         nvarchar(50)
    ) 



    insert into @tables 
    (
        schema_name,
        table_name,
        column_name,
        type_name 
    ) 
    select
        schema_name,
        table_name,
        column_name,
        type_name 
    from
    (
        select 
            schema_name,
            table_name, 
            column_name,
            type_name
        from
        (
            select name table_name, object_id table_id, schema_name = schema_name(schema_id)
            from sys.tables
        ) [Table]
        left join
        (
            select 
                object_id table_id, 
                name column_name, 
                type_name = type_name(system_type_id)
            from 
                sys.columns columns
        ) [column]
        on [column].table_id = [table].table_id
    ) tablecolumn
    where type_name = 'uniqueidentifier'
    order by table_name

    declare @sql         nvarchar(max);
    declare @schema_name     nvarchar(50);
    declare @table_name     nvarchar(50);
    declare @column_name     nvarchar(50);

    Create Table   #results 
    (
        query nvarchar(max),
        schema_name nvarchar(50),
        table_name nvarchar(255),
        column_name nvarchar(255),
        row_count int
    )

    declare table_cursor cursor for  
    select schema_name, table_name, column_name
    from @tables 

    open table_cursor   
    fetch next from table_cursor 
    into @schema_name, @table_name, @column_name    

    while @@fetch_STATUS = 0   
    begin     
        set @sql = 'insert into #results select ''select [' + 
        @column_name + '], *  from [' + @schema_name + '].[' +  @table_name + '] where [' + @column_name + 
        '] = ''''' + @query_text + ''''''' query, ''' + @schema_name + ''' [schema_name], ''' 
        + @table_name + ''' [table_name], ''' + @column_name + ''' [column_name], count(-1) row_count from [' 
        + @schema_name + '].[' +  @table_name + '] where [' + @column_name + '] =  ''' + @query_text + '''; ';
        print @sql;
        execute sp_ExecuteSQL @sql;
        
        fetch next from table_cursor 
        into @schema_name, @table_name, @column_name    
    end   

    close table_cursor   
    deallocate table_cursor 

    select * from @results where 0 < row_count order by row_count desc;
    Drop Table #results
end

go

exec #sp_find_guid_in_tables '<insert query GUID value here>'
drop procedure #sp_find_guid_in_tables