Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

how to search a text in a schema

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
gudii9
Asked:
gudii9
  • 5
  • 3
  • 2
  • +1
3 Solutions
 
ajexpertCommented:
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
 
sventhanCommented:
-- 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
 
sdstuberCommented:
"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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

I wrote it!

but other people have written similar loops too!  
0
 
sventhanCommented:
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
 
gudii9Author Commented:

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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
ajexpertCommented:
Does this screen shot help you?
I have modified a bit in order to protect our organization settings

Object-Search-TOAD.jpg
0
 
sventhanCommented:
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
 
gudii9Author Commented:
thank you very much
0
 
sdstuberCommented:
glad we could help
0

Featured Post

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!

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now