How can I search a schema for a particular value in all tables?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How can I search a schema for a particular value in all tables?

  1. #1
    Join Date
    Nov 2000
    Location
    Charlotte
    Posts
    88

    Question

    Is there a way to search a schema and get the table and column name(s) that holds a particular value?

    I'm need to find what column(s) in the schema that has the value "Charlotte" in it.

    Any ideas or hints would be greatly appreciated.

    Thanks,
    smoothyc

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    It should be store procedure or pl/sql block (for example like) :

    create or replace
    procedure find_in schema(p_owner varchar2, p_value varchar2) is
    cmd varchar2(1000);
    n_rows number;
    begin
    fop rt in (select table_name from all_tables where p_owner=owner) loop
    for rc in (select column_name from all_tab_columns
    where p_owner = owner and table_name = rt.table_name)
    cmd := 'select count(*) from '||p_owner||'.'||rt.table_name||
    ' where '|| rc.column_name ||'='''||rtrim(p_value)||''';
    -- or with like : ' where '|| rc.column_name ||' like ''%'||rtrim(p_value)||''%';
    execute immediate cmd into n_rows;
    if n_rows > 0 then
    dbms_output.put_line('Found value ('||p_value||') in table '||rt.table_name||'['||rc.column_name||']'
    dbms_output.put_line('# of rows with this value = '||to_char(n_rows));
    end if;
    end loop;
    end loop;
    end;
    /

    I DIDN'T TEST THIS PROCEDURE !!!

  3. #3
    Join Date
    Nov 2000
    Location
    Charlotte
    Posts
    88
    Thanks Shestakov.

    I'll take a closer look at this and see what I come up with. I'll post the results if/when I get them.

    Thanks again,
    smoothyc

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Note about impruve this script (procedure):

    1. check type of each columns
    LOOP 2 -->

    select column_name from all_tab_columns
    where p_owner = owner and table_name = rt.table_name
    AND DATA_TYPE in ('CHAR','VARCHAR2')

    2. trunc trailing blanks in CHAR fileds

    where rtrim('||rc.column_name||')='''||rtrim(p_value)||''';

    [Edited by Shestakov on 04-03-2002 at 02:41 PM]

  5. #5
    Join Date
    Nov 2000
    Location
    Charlotte
    Posts
    88
    Shestakov,

    I found the table.column that houses the value I was looking for. I was unable to get you PL/SQL script to work.

    However, I'm now on a quest to get that to work (or create one very similar). Thanks again for your help. I'll post the script, once I get it to work the way I need it to.

    Thanks again,
    smoothyc

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