CODEWITHSUNDEEP
sqloracle-database
Anand
Anand

Reputation: 12142

Search for a given string in all fields of an entire schema for Oracle

How can we Search for a given string in all fields of an entire schema for Oracle?

is there an sql script to do the above in Oracle? if yes, how?

Can we do it by any other script?

Upvotes: 4

Views: 4487

Answers (2)

Tufan Barış Yıldırım
Tufan Barış Yıldırım

Reputation: 3962

"ALL_TAB_COLUMNS" is a systable, have all table columns data type etc.

"USER_TAB_COLUMNS" is a systable, have all table columns data type etc (which owner is current user). (Thanks Rene)

An Example:

 SET SERVEROUTPUT ON SIZE 100000 -- maybe you have a lot of table and columns

    DECLARE
      matches INTEGER;
    BEGIN
      FOR columns IN (SELECT table_name, column_name FROM user_tab_columns where data_type = 'VARCHAR2') LOOP

        EXECUTE IMMEDIATE
          'SELECT COUNT(*) FROM '||t.table_name||' WHERE instr('||t.column_name||' , :1) > 0'
          INTO matches
          USING 'What you search';

        IF matches > 0 THEN
          dbms_output.put_line( t.table_name ||' '||t.column_name||' '||matches );
        END IF;

      END LOOP;

    END;
    /

this query will output table_name '' column_name'' and count, if you have standart column and table names, you can change query as IF columns > 0 then write a query UNION in loop and return the cursor, or return a table,

Upvotes: 4

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

Can we do it by any other script?

You can dump the database to disk as text, then use grep.

Upvotes: 0

Related Questions