Oracle PL/SQL - Search for String in All Tables of Schema

When doing database work I may have a string that I want to search through all tables for, especially if I am unfamiliar with the logical schema I am working with and I only have a string to help lead me to the proper table(s).

The following Oracle PL/SQL script can perform a case-insensitive search for a specified string in all tables under a specified schema owner on Oracle Database 11g or greater.

/* This script searches for a specified string in all character-based columns of all tables for the specified owner.
   Requires Oracle 11g or higher due to passing a CLOB to "execute immediate" */

SET SERVEROUTPUT ON SIZE 100000

DECLARE
    -- Number of matches in current table
    v_match_count INTEGER;
    
    -- If the counter is 0 for the current statement being constructed no OR operator is needed before the column
    v_counter INTEGER;
    
    -- The owner of the tables being searched
    v_owner VARCHAR2(255) := 'OWNER_NAME';
    -- A string that is part of the data type(s) to look at
    v_data_type VARCHAR2(255) := 'CHAR';
    -- The string to be searched for
    v_search_string VARCHAR2(4000) := 'FIND_ME';
    
    -- Store the SQL to execute for each table in a CLOB to get around the 32767 byte max size for a VARCHAR2
    v_sql CLOB := '';
BEGIN
    -- Set the current schema to the schema specified in the v_owner variable
    EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = ' || v_owner;

    -- Loop over a set of tables, sorted by table name
    FOR cur_tables IN  (SELECT
                           at.owner, at.table_name
                        FROM
                           ALL_TABLES at
                        WHERE
                           at.owner = v_owner AND at.table_name IN 
                               (SELECT
                                    atc.table_name
                                FROM
                                    all_tab_columns atc
                                WHERE
                                    atc.owner = at.owner AND atc.data_type LIKE '%' || UPPER(v_data_type) || '%')
                        ORDER BY
                            at.table_name) LOOP
        v_counter := 0;
        v_sql := '';
        
        -- Loop over the columns in the current table
        FOR cur_columns IN (SELECT
                                atc.column_name
                            FROM
                                all_tab_columns atc
                            WHERE 
                                atc.owner = v_owner AND atc.table_name = cur_tables.table_name 
                                AND atc.data_type LIKE '%' || UPPER(v_data_type) || '%') LOOP
            -- If this is not the first column, add an or to the SQL statement
            IF v_counter > 0 THEN
                v_sql := v_sql || ' OR ';
            END IF;
            -- Compare the column with the string being searched for in a case-insensitive manner
            v_sql := v_sql || 'UPPER(' || cur_columns.column_name || ') LIKE ''%' || UPPER(v_search_string) || '%''';
            v_counter := v_counter + 1;
        END LOOP;
        
        -- Get the number of records in the table that contain the string being searched for
        v_sql := 'SELECT COUNT(*) FROM ' || cur_tables.table_name || ' WHERE ' || v_sql;
        
        EXECUTE IMMEDIATE v_sql
        INTO v_match_count;
        
        -- Output to screen if there are any matches in the current table
        IF v_match_count > 0 THEN
            DBMS_OUTPUT.PUT_LINE('Match in ' || cur_tables.owner || '.' || cur_tables.table_name || ' - ' || v_match_count || ' record(s)');
        END IF;
    END LOOP;
    
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error when executing the following: ' || DBMS_LOB.SUBSTR(v_sql, 32600));
END;

The v_sql variable is a CLOB variable rather than a VARCHAR2 variable so that the SQL generated to search an individual table can be up to 4 GB. A VARCHAR2 variable in PL/SQL can only hold up to 32767 bytes, and if a table has a large amount of columns this limit can be reached. Passing the CLOB variable to the EXECUTE IMMEDIATE statement is also why this script requires Oracle 11g or greater. The EXECUTE IMMEDIATE statement does not accept CLOB variables before Oracle 11g.

The script above is based on this answer on stackoverflow.

I modified the script so that a single SQL statement is generated for each table instead of a single SQL statement for every column of each table because that was too slow when working with many large tables. The downside is that the script only reports tables with a match rather than giving the table and column with a match. This meets my needs because once I have the correct table(s) I can find what I need pretty easily. The SQL generated and executed for each table is in the following form:

SELECT COUNT(*) FROM table WHERE UPPER(column1) LIKE '%FIND_ME%' OR UPPER(column2) LIKE '%FIND_ME%' OR ...

The first SQL statement looped through in the script (where all table names are retrieved) is ordered alphabetically by table name so that if the search is a long-running process I can check-up on the table being queried periodically and get a general idea of the total search progress using something similar to the following query:

-- Check on what my other session are doing (excludes the current session)
SELECT
    ses.sid, ses.osuser, ses.program, ses.schemaname, 
    ROUND(TO_NUMBER(SYSDATE - ses.sql_exec_start) * 86400) AS "Seconds Executing", st.sql_text
FROM
    v$session ses INNER JOIN 
    v$sqltext st ON ses.sql_address = st.address AND ses.sql_hash_value = st.hash_value
WHERE
    UPPER(osuser) LIKE '%MY_OS_USER%' AND ses.sid <> SYS_CONTEXT('USERENV', 'SID') 
ORDER BY
    ses.sid, st.piece;

The sql_text column will contain currently executing SQL queries. The "Seconds Executing" column is useful if a very large table is being queried.

Please feel free to ask questions or provide feedback via comments on this page. I will post how to search for a string in all tables of a SQL Server database using a T-SQL script in a future article.