Find string in all tables of a certain schema Oracle.

book

Article ID: 211799

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

Here is a script that returns all tables in a certain schema containing a specified string. In this example the schema is AUTOMIC and the string is FTPAGENT01.

SET SERVEROUTPUT ON;
DECLARE
  match_count INTEGER;

--the owner/schema of the tables you are looking at
  v_owner VARCHAR2( 255) :='AUTOMIC' ;

-- data type you look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2( 255) :='VARCHAR2' ;

--The value you are looking for with like "%" operator
  v_search_string VARCHAR2(4000) := '%FTPAGENT01%' ;

BEGIN
  FOR t IN (SELECT atc.table_name
                  ,atc.column_name
                  ,atc.owner 
            FROM all_tab_cols atc
           WHERE atc.owner = v_owner
             AND data_type =  v_data_type
             -- esclude vir. columns
             AND atc.column_id is not null
             -- exclude views
             AND not exists (select 1 
                               from all_views 
                               where view_name = atc.table_name) ) LOOP

    EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM ' 
         || t.owner || '.'  ||t. table_name|| 
        ' WHERE UPPER("'||t.column_name ||'") LIKE  UPPER(: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;
/

Environment

Release : 12.3

Component : AUTOMATION ENGINE

Resolution

This is the output.

OH OH_NAME 1
HOST HOST_PATHBIN 1
HOST HOST_PATHJOBREPORT 1
HOST HOST_PATHTEMP 1
HOST HOST_SMDISPLAYNAME 1
MELD MELD_MSGINSERT 22
RT RT_MSGINSERT 3
ACTEX ACTEX_HOST 1
MQSRV MQSRV_NAME 1
OVW OVW_VVALUE 1
AH AH_INFO 142
AH AH_HOSTDST 143
AH AH_HOSTSRC 1
AH AH_FILENAMESRC 1
AH AH_NAME 144
AH AH_ALIAS 1


PL/SQL procedure successfully completed.