--    query_text (parameter):  The query_text parameter is the search string value.  Please note that this query text will be used in a like comparison.
--    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 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_string_in_tables @query_text nvarchar(255)
as
begin
    Declare @tables as Table
    (
        schema_name varchar(50), table_name varchar(255),
        column_name varchar(255), type_name varchar(50)
    )


    insert into @tables select * 
    from
    (
        select  schema_name, table_name, column_name, type_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, 
            system_type_id type_id, type_name = type_name( system_type_id )
            from     sys.columns columns
        ) [column]
        on [column].table_id = [table].table_id
    ) tablecolumn
    where type_name like '%char%' or type_name like '%text%'
    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 + ']  like ''''%' + @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 + ']  like ''%' + @query_text + '%''';
        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_string_in_tables '<insert query string value here>'

drop procedure #sp_find_string_in_tables