Find Columns that have a particular Value

2 steps

Generate your select statements

SELECT 'SELECT ''' || T1.OWNER || '.' || T1.TABLE_NAME || '.' || T1.COLUMN_NAME || ':'', ' || T1.COLUMN_NAME || ' FROM ' || T1.OWNER || '.' || T1.TABLE_NAME || ' WHERE ' || T1.COLUMN_NAME || ' = ''' || '%YOUR_COLUMN_VALUE%' || ''';'

FROM SYS.ALL_TAB_COLUMNS T1

WHERE T1.COLUMN_NAME LIKE '%COLUMN_NAME%'

and T1.OWNER LIKE '%SCHEMA_NAME_GOES_HERE%'

ORDER BY T1.OWNER, T1.COLUMN_NAME ;

and then execute that statements.

If your column is a number or a date you must adjust the select statement to get the correct one.

The pipeline version:

SQL> select * from table(SearchTables(filter=>'SALE%'));

OWNER      TABLE_NAME COLUMN_NAM ROW_ID               VALUE
---------- ---------- ---------- -------------------- ----------
BILLYV     DEPT       DNAME      AAAUR8AABAAAPvRAAC   SALES
BILLYV     EMP        JOB        AAAUR+AABAAAR4ZAAB   SALESMAN
BILLYV     EMP        JOB        AAAUR+AABAAAR4ZAAC   SALESMAN
BILLYV     EMP        JOB        AAAUR+AABAAAR4ZAAE   SALESMAN
BILLYV     EMP        JOB        AAAUR+AABAAAR4ZAAJ   SALESMAN

5 rows selected.

SQL> select * from table(SearchTables(filter=>'AL%'));

OWNER      TABLE_NAME COLUMN_NAM ROW_ID               VALUE
---------- ---------- ---------- -------------------- ----------
BILLYV     EMP        ENAME      AAAUR+AABAAAR4ZAAB   ALLEN

1 row selected.

The code:

create or replace type TStrings is table of varchar2(4000); 
/ 
 
create or replace type TRowMatch is object( 
       owner          varchar2(128), 
       table_name     varchar2(128), 
       column_name    varchar2(128), 
       row_id         varchar2(100), 
       value          varchar2(4000) 
); 
/ 
 
create or replace type TRowMatchTable is table of TRowMatch; 
/ 
 
create or replace function SearchTables( owner varchar2 default user(), tableName varchar2 default '%', filter varchar2 ) 
return TRowMatchTable pipelined is 
       -- for making pretty dynamic SQL 
       TAB            constant varchar2(1)   := chr(9); 
       LF             constant varchar2(1)   := chr(10); 
 
       c              integer;               -- dbms sql cursor handle 
       cur            sys_refcursor; 
       rc             integer;               -- dbms sql return code 
 
       colsList       TStrings;              -- string cols to search 
       buffer         TStrings;              -- string cols matches found 
       rid            varchar2(50);          -- rowid of row with matching col(s) 
 
       -- dynamic SQL 
       sqlProjection  varchar2(32767); 
       sqlPredicate   varchar2(32767); 
       dynamicSQL     varchar2(32767); 
begin 
       for tabList in( 
               select 
                       owner, table_name 
               from   all_tables 
               where  owner like SearchTables.owner 
               and    table_name like SearchTables.tableName 
               order by 1,2 
       ) loop 
               -- get the string columns in the table 
               select 
                       column_name bulk collect into colsList 
               from   all_tab_cols 
               where  owner = tabList.owner 
               and    table_name = tabList.table_name 
               and    data_type like '%CHAR%' 
               order by column_id; 
 
               -- if no string colums then skip table 
               if colsList.count = 0 then 
                       exit; 
               end if; 
 
               -- build dynamic SQL using colums on table 
               sqlProjection := null; 
               sqlPredicate := null; 
               for i in 1 .. colsList.count loop 
                       if i = colsList.count then 
                               sqlProjection := sqlProjection||TAB||colsList(i); 
                       else 
                               sqlProjection := sqlProjection||TAB||colsList(i)||','||LF; 
                       end if; 
 
                       if i = 1 then 
                               sqlPredicate := 'from'||TAB||tabList.table_name||LF; 
                               sqlPredicate := sqlPredicate||'where'||TAB||colsList(i)||' like :Filter'||LF; 
                       else 
                               sqlPredicate := sqlPredicate||'or'||TAB||colsList(i)||' like :Filter'||LF; 
                       end if; 
               end loop; 
               dynamicSQL := 'select rowid as RID, TStrings('||LF||sqlProjection||LF||') as COL'||LF||sqlPredicate; --'
       --     dbms_output.put_line( LF||'Dynamic SQL:'||LF||dynamicSQL ); 
 
               -- we need to parse and bind dynamically 
               c := DBMS_SQL.Open_Cursor(); 
               DBMS_SQL.Parse( c, dynamicSQL, DBMS_SQL.NATIVE ); 
               DBMS_SQL.Bind_Variable( c, 'Filter', filter ); 
               rc := DBMS_SQL.execute( c ); 
 
               -- convert to a refcursor - we can use a static coded fetch as we know 
               -- the SQL projection returned is a rowid and column of type TStrings 
               cur := DBMS_SQL.to_RefCursor( c ); 
 
       --     dbms_output.put_line ( 'Filter: <column> like '''||STRING_FILTER||'''' ); 
               loop 
                       fetch cur into rid, buffer; 
                       exit when cur%NotFound; 
 
                       -- determine which columns matches 
                       for i in 1 .. buffer.count loop 
                               if buffer(i) like filter then 
                                       pipe row( 
                                               TRowMatch( 
                                                       tablist.owner, 
                                                       tabList.table_name, 
                                                       colsList(i), rid, buffer(i) 
                                               ) 
                                       ); 
                               end if; 
                       end loop; 
               end loop; 
 
               close cur; 
       end loop; 
       return; 
end; 
/