Solved

how to search a text in a schema

Posted on 2009-04-15
12
378 Views
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.
0
Comment
Question by:gudii9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 14

Assisted Solution

by:ajexpert
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.
 
HTH
0
 
LVL 18

Accepted Solution

by:
sventhan earned 250 total points
ID: 24150185
-- found this from ee.
DECLARE
    v_search VARCHAR2(50) := 'find this string';
    v_dummy  NUMBER;
BEGIN
    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)
    LOOP
        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
        THEN
            DBMS_OUTPUT.put_line(RPAD(x.owner, 35) || RPAD(x.table_name, 35) || x.column_name);
        END IF;
    END LOOP;
END;
 

0
 
LVL 74

Assisted Solution

by:sdstuber
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?
0
Technology Partners: 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!

 
LVL 74

Expert Comment

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

I wrote it!

but other people have written similar loops too!  
0
 
LVL 18

Expert Comment

by:sventhan
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).
 
0
 
LVL 7

Author Comment

by:gudii9
ID: 24150338

when i did like

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

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

says
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
0
 
LVL 74

Expert Comment

by:sdstuber
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

0
 
LVL 74

Expert Comment

by:sdstuber
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!  

:)
0
 
LVL 14

Expert Comment

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

Object-Search-TOAD.jpg
0
 
LVL 18

Expert Comment

by:sventhan
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.
0
 
LVL 7

Author Closing Comment

by:gudii9
ID: 31570558
thank you very much
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24220027
glad we could help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup
Suggested Courses

628 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