Search all tables for a certain data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Search all tables for a certain data

  1. #1
    Join Date
    May 2004
    Location
    Central FL
    Posts
    12

    Search all tables for a certain data

    All, I have an interesting problem for which I need some advice. I need to search an Oracle database for a piece of data...don't have the table name, or the column name...only have the data that needs to be searched (say for example "JOHN SMITH").

    So how can I write a sproc, that will loop through each user table, then in each row/column and search for JOHN SMITH? If I am dealing with about 50 tables or so, would it just be better to use the Schema Browser in TOAD and just eye-ball it? Is there a tool available that would let me do some type of global search, for example like the Windows search? Thanks!!

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Nice business requirement.

    What you need is a query that would write the query you need, here are the specs:

    1- select from dba_tab_columns where schema is one of the schemas you want to search AND the column has a datatype like varchar2 -I suppose you don't want to search date and number columns, don't you?

    2- rework your query to add the "select" syntax including a where clause in the form "liike '%JOHN SMITH%'.

    3- last but not least don't forget to wrap column name on upper() function.

    Your query should provide you one query for each searchable column.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    May 2004
    Location
    Central FL
    Posts
    12
    omg that worked so good!

    Here is my query:

    Code:
    select 'select * from ' || table_name || ' where upper(' || column_name || ') LIKE ''%JOHN_SMITH%'''
      from dba_tab_columns
     where table_name LIKE 'NAME%' and
           data_type = 'VARCHAR2'

    Like you said this gave one query for each searchable column, and we knew the tables we wanted were prefixed with NAME...there were like 500 results, which is fine because I just exported them to an Excel spreadsheet and split them up between the team to run . Thank you very much!

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    glad it worked fine, you know... "to protect and serve"
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width