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.