search_all_tables.sql

DECLARE 
    match_count     INTEGER; 
    v_search_string VARCHAR2(4000) := 'C1'; 
    
BEGIN 
    FOR t IN (SELECT owner, 
                     table_name, 
                     column_name 
              FROM  all_tab_columns 
              WHERE  owner in ('AGR56') and data_type IN ( 'CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2' )) LOOP 
        BEGIN 
            EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||t.owner|| '.'|| t.table_name|| ' WHERE '||t.column_name||' = :1' INTO match_count
            USING v_search_string; 

            IF match_count > 0 THEN 
              dbms_output.Put_line(t.owner 
                                   || '.' 
                                   || t.table_name 
                                   ||' ' 
                                   ||t.column_name 
                                   ||' ' 
                                   ||match_count); 
            END IF; 
        EXCEPTION 
            WHEN OTHERS THEN 
              dbms_output.Put_line('Error encountered trying to read ' 
                                   || t.column_name 
                                   || ' from ' 
                                   || t.owner 
                                   || '.' 
                                   || t.table_name); 
        END; 
    END LOOP; 
END;