4

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?

Anand
  • 11,872
  • 10
  • 39
  • 51

2 Answers2

4

"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,

Tufan Barış Yıldırım
  • 3,952
  • 26
  • 36
  • 2
    I don't think anybody uses data_type 'VARCHAR' anymore. So you may want to change this script to include the data types you do use to store strings. VARCHAR2, CHAR, NVARCHAR2, CLOB. Also you may want to search for instr('what you search')>0 if you don't want exact matches. Use user_tab_columns to restrict your search to tables in your schema only. – Rene Apr 06 '11 at 07:13
  • 1
    I guess it should be "IF matches > 0 THEN" instead of "columns", isn't it? – anilech Aug 13 '15 at 07:07
  • If you are running this in SQL Developper, don't forget to [enable DBMS Output](http://www.thatjeffsmith.com/archive/2012/05/enabling-dbms_output-by-default-in-sql-developer/) – Bibz May 17 '16 at 13:36
0

Can we do it by any other script?

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

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185