how to search a text in a schema

Posted on 2009-04-15
Last Modified: 2013-12-18
I have 100 tables in my schema. I would like to search say word 'hello' in my schema.How can I search it quicly and effectively in my entire schema in all tables. Any sample code, resources, ideas, links highly appreciated. Thanks in advance.
Question by:gudii9
  • 5
  • 3
  • 2
  • +1
LVL 14

Assisted Solution

ajexpert earned 125 total points
ID: 24150106
If you have TOAD, this can be done easily.
Search->Object Search and enter the Search Term.
Click the Schema's, Object Names, Source etc.
This can be done via script too.  Let me know if you need script.
LVL 18

Accepted Solution

sventhan earned 250 total points
ID: 24150185
-- found this from ee.
    v_search VARCHAR2(50) := 'find this string';
    v_dummy  NUMBER;
    DBMS_OUTPUT.put_line(RPAD('Table Owner', 35) || RPAD('Table Name', 35) || 'Column Name');
    FOR x IN (  SELECT owner, table_name, column_name
                  FROM all_tab_columns
                 WHERE data_type IN ('VARCHAR2', 'CHAR')  -- change needed
              ORDER BY owner, table_name, column_name)
        EXECUTE IMMEDIATE   'select count(*) from '
                         || x.owner
                         || '.'
                         || x.table_name
                         || ' where '
                         || x.column_name
                         || ' = '''
                         || v_search
                         || ''' and rownum = 1'
            INTO v_dummy;
        IF v_dummy = 1
            DBMS_OUTPUT.put_line(RPAD(x.owner, 35) || RPAD(x.table_name, 35) || x.column_name);
        END IF;

LVL 74

Assisted Solution

sdstuber earned 125 total points
ID: 24150243
"quickly"  ?

unless your tables are all small, you can't do this "quickly"

do you know what columns you want to search?  or do you want to search every column of every table?
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

LVL 74

Expert Comment

ID: 24150260
sventhan, I was just about to post that same code block  :)

I wrote it!

but other people have written similar loops too!  
LVL 18

Expert Comment

ID: 24150332
I agree, the whole credit should go to you. This piece of code should be a HOT could be saved in a place here in EE for the future use.
If its works please spilit the points with the owner of this code (sdstuber).

Author Comment

ID: 24150338

when i did like

 In TOAD,Search->Object Search and enter the Search Term.

Click the Schema's, Object Names, Source etc.

ORA-00942: table or view does not exist
Instead of searching for the data looks like searching for table name.Any specific options to select to search on data. Please advise
LVL 74

Expert Comment

ID: 24150465
don't worry about the split,  I just thought it was funny to have my own code beat me:)

for the TOAD error, you were probably searching through objects you don't have privileges to do SELECT on.

the pl/sql block above should be safe because it uses the ALL_TAB_COLUMNS view which should only be populated with the tables you can query

LVL 74

Expert Comment

ID: 24150478
let me clarify....don't worry about splitting points for code I posted in another question.
you can give me a split if I posted something else here that is helpful though!  

LVL 14

Expert Comment

ID: 24150519
Does this screen shot help you?
I have modified a bit in order to protect our organization settings

LVL 18

Expert Comment

ID: 24150773
Sounds Great SD.
In order to make it work this in TOAD you should be having access to dba data dictionary views.  Please ask your dba to grant you "SELECT_CATALOG_ROLE". Grant this role to allow users SELECT privileges on data dictionary views.
If you have a user with DBA role/privileage use that one for the search.
Did you try the code snippet that I've posted? It worked great for me and other people here in ee.

Author Closing Comment

ID: 31570558
thank you very much
LVL 74

Expert Comment

ID: 24220027
glad we could help

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
DB migration from Mssql to 12c oracle , data not loading. 3 48
Email query results in HTML 6 38
Toad 12.10 Enterprise visual interface 4 33
Help with Oracle IF statment 5 36
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question