Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Looking for a certain value in DB

    Is there a way to look at a given schema (schema 'XYZ') and look at every column on every table, looking for a value of 35006 or '35006'? I don't know if one one table it may be an integer, or text on another table.

    Basically, I need to find a needle in the proverbial haystack.

  2. #2
    Join Date
    Jan 2004
    Posts
    99
    that is good question, as far as I know this is a manual adhoc process....oracle text provides this functionality I think.

    ps. other members of the forum may have some good suggestions.

  3. #3
    Join Date
    May 2006
    Posts
    132
    Yep...look at Note: 243096.1 on Metalink.

  4. #4
    Join Date
    Sep 2005
    Posts
    220
    I don't have access to metalink

  5. #5
    Join Date
    Sep 2005
    Posts
    220
    Here's what I have so far. I can't however figure out how to print out the table/column name when it does find a match. Any ideas ????

    Code:
    SET termout OFF echo OFF feed OFF trimspool ON head ON pages 0
    
    spool c:\DATA\countall.tmp
    
    -- Text values
    SELECT 'select * from '||table_name||' where '||column_name||' like ''%35006%'''||';'
    FROM user_tab_columns
    WHERE data_type = 'VARCHAR2'
    
    
    --SELECT 'select * from '||table_name||' where '||column_name||' = 35006;'
    --FROM user_tab_columns
    --WHERE data_type = 'NUMBER'
    /
    spool OFF
    
    SET termout ON
    @@c:\DATA\countall.tmp
    
    SET head ON feed ON

Posting Permissions

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