How to search all tables for one value

Question

I have a database with more than 100 tables. I want to find the table that contains the 'ATMOSPHERIC' data in the tables in this database. How can I do it. I wrote such a code myself but it did not work. I am waiting for your advice.

DECLARE
  v_search_term VARCHAR2(100) := 'ATMOSFERIK';
  v_sql         VARCHAR2(4000);
  v_result      NUMBER;
BEGIN
  FOR t IN (SELECT *
            FROM all_tab_columns
            WHERE data_type LIKE '%CHAR%' OR data_type LIKE '%CLOB%'
            ORDER BY table_name, column_name)
  LOOP
    v_sql := 'SELECT COUNT(*) FROM myDB.' || t.table_name ||
             ' WHERE ' || t.column_name || ' LIKE ''%' || v_search_term || '%''';
    EXECUTE IMMEDIATE v_sql INTO v_result;

    IF v_result > 0 THEN
      DBMS_OUTPUT.PUT_LINE('Table: SMS.' || t.table_name || ', Column: ' || t.column_name);
    END IF;
  END LOOP;
END;

Edit: Error report - ORA-00942: table or view does not exist ORA-06512: at line 13 00942. 00000 - "table or view does not exist" *Cause:
*Action:

What do you mean by 'it did not work'? it gave an error or did not return the results you expect? — gsalem, Jul 21 '23 at 07:10

score 2 · Accepted Answer · answered Jul 21 '23 at 07:42

Use quoted identifiers and filter on the owner:

DECLARE
  v_owner       VARCHAR2(30)  := 'MYDB';
  v_search_term VARCHAR2(100) := 'ATMOSFERIK';
  v_sql         VARCHAR2(4000);
  v_result      NUMBER;
BEGIN
  FOR t IN (
    SELECT owner, table_name, column_name
    FROM   all_tab_columns
    WHERE (data_type LIKE '%CHAR%' OR data_type LIKE '%CLOB%')
    AND    owner = v_owner
    ORDER BY owner, table_name, column_name
  )
  LOOP
    v_sql := 'SELECT COUNT(*)'
          || ' FROM   "' || t.owner || '"."' || t.table_name || '"'
          || ' WHERE  "' || t.column_name || '" LIKE ''%' || v_search_term || '%''';
    EXECUTE IMMEDIATE v_sql INTO v_result;

    IF v_result > 0 THEN
      DBMS_OUTPUT.PUT_LINE('Table: SMS.' || t.table_name || ', Column: ' || t.column_name);
    END IF;
  END LOOP;
END;
/

Then if you have the sample data:

CREATE TABLE mydb.table_name (a, b, c, d) AS
SELECT 'x', DATE '2023-01-01', 42, 'ATMOSFERIK' FROM DUAL;

CREATE TABLE mydb."lower_case_table_name" ("a", "b", "c", "d") AS
SELECT 'x', DATE '2023-01-01', 'ATMOSFERIK', 42 FROM DUAL;

Then the output is:

Table: SMS.TABLE_NAME, Column: D
Table: SMS.lower_case_table_name, Column: c

fiddle

How to search all tables for one value

1 Answers1