1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle find all tables with a column value

Discussion in 'Other Development Tools' started by sameer, Oct 4, 2008.

  1. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    Many times we are stuck when we want to find all tables that have a particular value in a particular column.

    This code when executed at SQL*Plus prompt, builds and runs SQL to find all user tables with a particular column and value, spools the results to a file, and opens the file in Notepad.

    This little script solicits a column name and value at the SQL*Plus prompt, builds and runs SQL to find all user tables with that column and value, spools the results to a file, and opens the file in Notepad. As virtual tables, views will also register as hits.

    The SQL*Plus spool, edit, and execute operations default to the Oracle BIN directory on a Windows PC (c:\orant\bin or its equivalent). And a .SQL extension is assumed when a file is executed with no extension. So if you put this script in the BIN directory as FCV.SQL you can then execute it at the SQL*Plus prompt just by entering @FCV

    Note: This script works fine against columns of common character and number datatypes , but it does not work against columns of certain datatypes such as LONG.

    Code (Text):

    rem *****************************************************************
      clear screen
    prompt ***************************************
    prompt *                                     *
    prompt *  This reveals the user tables with  *
    prompt *  a column of a particular value.    *
    prompt *                                     *
    prompt *  Enter a column name:               *
    prompt *                                     *
    prompt ***************************************
      accept in_column char
    prompt ***************************************
    prompt *                                     *
    prompt *  Enter a value:                     *
    prompt *                                     *
    prompt ***************************************
      accept in_value char
      set echo     off
      set recsep   wrapped
      set termout  off
      set feedback off
      set heading  off
      set linesize 80
      set pagesize 0
      set verify   off
      spool fval.sql
      select 'select distinct '||''''||table_name||''''||'
              from '||table_name||'
              where  upper (&in_column) = upper ('||'''&in_value'''||');'
      from  user_tab_columns
      where column_name = upper ('&in_column');
      spool off
      spool fval.txt
      start fval.sql
      spool off
      set termout  on
      set feedback 6
      set heading  on
      set pagesize 24
      set verify   on
      pause Done...  Press ENTER to view results
      edit fval.txt
     
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Another brilliant solution from Tom Kyte

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5203164092530

    You have to pass the string to search and it will search whole the tables of the scheme and gives the output. Here also LONG and CLOB are Exceptions .


    Code (Text):

    ops$tkyte@ORA920.US.ORACLE.COM> create or replace
      2  procedure find_string( p_str in varchar2 )
      3  authid current_user
      4  as
      5      l_query    long;
      6      l_case     long;
      7      l_runquery boolean;
      8      l_tname    varchar2(30);
      9      l_cname    varchar2(30);
     10          type rc is ref cursor;
     11          l_cursor   rc;
     12  begin
     13      dbms_application_info.set_client_info( '%' || upper(p_str) || '%' );
     14
     15      for x in (select * from user_tables )
     16      loop
     17          l_query := 'select distinct ''' || x.table_name || ''', $$
     18                        from ' || x.table_name || '
     19                       where ( 1=0 ';
     20          l_runquery := FALSE;
     21                  l_case     := NULL;
     22          for y in ( select *
     23                       from user_tab_columns
     24                      where table_name = x.table_name
     25                        and data_type in ( 'VARCHAR2', 'CHAR' )
     26                   )
     27          loop
     28              l_runquery := TRUE;
     29              l_query := l_query || ' or upper(' || y.column_name ||
     30                         ') like userenv(''client_info'') ';
     31              l_case := l_case || '||'' ''|| case when upper(' || y.column_name ||
     32                        ') like userenv(''client_info'') then ''' ||
     33                        y.column_name || ''' else NULL end';
     34          end loop;
     35          if ( l_runquery )
     36          then
     37              l_query := replace( l_query, '$$', substr(l_case,8) ) || ')';
     38              begin
     39                                  open l_cursor for l_query;
     40                                  loop
     41                                          fetch l_cursor into l_tname, l_cname;
     42                                          exit when l_cursor%notfound;
     43                          dbms_output.put_line
     44                          ( 'Found in ' || l_tname || '.' || l_cname );
     45                                  end loop;
     46                                  close l_cursor;
     47              end;
     48          end if;
     49
     50      end loop;
     51  end;
     52  /
     
  3. amaura

    amaura Guest

    Hi,
    You could also use a tool to search values, for example, this one is for free and source code is available. You'll need to install the Oracle ODBC driver and provide a DSN.

    sites.google.com/site/freejansoft/dbsearch-1