Search a term in the database (1): Which column(s) of which table(s) store this term

Sometimes, you know that some term is stored somewhere in the database, you can see it on the display, in the app or browser, and you know it comes from the database, but you don't know from which table(s).

For this purpose, you can run this anonymous block from Toad or SQL Developer and find out where is it stored. There are just two variables - owner schema (if you run it as sys or system user) and term we are looking for. As result comes text output - each line contains table name, column name and number of records with our term. It is of course possible to run it from SqlPlus with parameter, or to compile as a procedure or subprogramm in some package.

DECLARE
TYPE vchar_t IS TABLE OF VARCHAR2(50);

l_tbl      vchar_t;
l_col      vchar_t;
l_owner    VARCHAR2(50) := 'SCOTT';
l_cnt      NUMBER;
l_srcword  VARCHAR2(255):= 'John';
BEGIN

SELECT object_name BULK COLLECT INTO l_tbl
 FROM dba_objects
 WHERE object_type='TABLE'
 AND not object_name like '%$%'
 AND owner = l_owner
 ORDER BY 1;

 FOR ind in l_tbl.FIRST .. l_tbl.LAST
  LOOP
    SELECT column_name BULK COLLECT INTO l_col
     FROM dba_tab_columns
    WHERE owner = l_owner
      AND data_type like '%CHAR_'
      AND table_name = l_tbl(ind);
   
    IF l_col.count > 0 THEN
     FOR ind1 in l_col.FIRST .. l_col.LAST
      LOOP
      EXECUTE IMMEDIATE
      'SELECT COUNT(*) FROM ' || l_owner || '.' || l_tbl(ind)
           || ' WHERE ' || l_col(ind1) || ' LIKE ''%' || l_srcword || '%'''
       INTO l_cnt;
       IF l_cnt > 0 THEN
         dbms_output.put_line(l_tbl(ind) || '.' || l_col(ind1) || ': ' || l_cnt);
       END IF;
      END LOOP;
    END IF;   
  END LOOP;

  EXCEPTION
    WHEN OTHERS THEN dbms_output.put_line('Error: ' || SQLERRM);

END;