Search any text string in all tables of current schema in Oracle

A procedure is given below to search any text string in all tables of current schema in Oracle.

Create the following procedure in your schema:

CREATE OR REPLACE PROCEDURE search_text (i_search_string IN VARCHAR2)
IS
   CURSOR c_search
   IS
      SELECT    ‘Select ”’
             || table_name
             || ”’, ‘
             || SUBSTR (REPLACE (fields, ‘,’, ‘||” ”||’), 1, 4000)
             || ‘ From ‘
             || table_name
             || ‘ Where instr(lower(‘
             || REPLACE (fields, ‘,’, ‘||’)
             || ‘), ‘
             || ””
             || LOWER (i_search_string)
             || ””
             || ‘) > 0 ‘
                sqlcmd
        FROM (  SELECT wm_concat (column_name) fields, table_name
                  FROM (  SELECT table_name, column_name
                            FROM user_tab_cols
                           WHERE data_type = ‘VARCHAR2’
                        ORDER BY table_name, column_id)
              GROUP BY table_name);

   TYPE t_rec_data IS RECORD (
      table_name   VARCHAR2 (100),
      v_data       VARCHAR2 (4000)
   );

   TYPE rec_data IS TABLE OF t_rec_data
                       INDEX BY BINARY_INTEGER;

   v_rec_data     rec_data;
   i              NUMBER := 0;
BEGIN
   DBMS_OUTPUT.enable (200000);

   FOR c IN c_search
   LOOP
      BEGIN
         EXECUTE IMMEDIATE c.sqlcmd
            INTO v_rec_data (i).table_name, v_rec_data (i).v_data;

         i := i + 1;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   END LOOP;

 IF v_rec_data.COUNT > 0
   THEN
      FOR j IN v_rec_data.FIRST .. v_rec_data.LAST
      LOOP
         DBMS_OUTPUT.put_line (
            v_rec_data (j).table_name || ‘ | ‘ || v_rec_data (j).v_data);
      END LOOP;
   END IF;
END;

And then call it using the following:

set serveroutput on;

BEGIN
   search_text (‘marketing’); — where marketing is the string being searched
END;

It will list out the table names and the row data containing that string.

2 Comments on "Search any text string in all tables of current schema in Oracle"

  1. Thanks for the information. But I'm not clear what is "wm_concat "?

  2. wm_concat function available in wmsys schema, btw you can use listagg function instead, here is the modified cursor query:

    SELECT 'Select ''' || table_name || ''', ' || SUBSTR (REPLACE (fields, ',', '||'' ''||'), 1, 4000) || ' From ' || table_name || ' Where instr(lower(' || REPLACE (fields, ',', '||') || '), ' || '''' || LOWER (i_search_string) || '''' || ') > 0 ' sqlcmd FROM ( SELECT listagg(column_name,',') within group (order by column_name) fields, table_name FROM ( SELECT table_name, column_name FROM user_tab_cols WHERE data_type = 'VARCHAR2' ORDER BY table_name, column_id) GROUP BY table_name); – See more at: https://www.foxinfotech.in/2015/08/search-any-text-string-in-all-tables-oracle.html#sthash.o06C6S6g.dpuf

Comments are closed.