Oracle Procedure to Search for a Particular String/Character

 

Oracle Procedure to Search for a Particular String/Character

 

Oracle Procedure to Search for a Particular String/Character - Full Schema Scan

The below mentioned Oracle procedure will search for any particular string/character in all the tables for a particular schema and will list the count of number of rows for every column where that string/character is found. The code can be altered as per the need.

DECLARE

  match_count INTEGER;

  v_owner VARCHAR2(255) :='ENTER_SCHEMA_HERE';

  v_data_type VARCHAR2(255) :='VARCHAR2';

-- Type the string you are looking at

  v_search_string VARCHAR2(4000) := '@';

BEGIN

  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP

    EXECUTE IMMEDIATE

    'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' like ||':1'

    INTO match_count

    USING v_search_string;

    IF match_count > 0 THEN

      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );

    END IF;

  END LOOP;

END;

/