Re: How to select all tables that have the same column name and column value

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Thu, 15 Jan 2009 10:52:21 -0800 (PST)
Message-ID: <c65db2fe-e4ba-4350-a390-0aa9ea062d56_at_s9g2000prg.googlegroups.com>

On Jan 15, 9:25 pm, Homer <HomerS..._at_gmail.com> wrote:
> Hi everybody,
>
> I know how to select out all the tables that have the same column name
> using SELECT TABLE_NAME FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME =
> 'column_name'.  I need to take it a step further to select only those
> tables that have the same column name and a certain field value.
> For instance, I want all the tables that have an element called
> last_update_date and the last_update_date = '31-dec-08'.  Is it
> possible to do that?
>
> Thanks,
> Jon

Not with a single query, but easily in a PL/SQL loop with dynamic SQL inside. Something like this would do:

set serveroutput on size 100000

declare

   c cursor;
   f number;
begin
  for tbl in (select owner, table_name

                from all_tab_columns
               where column_name = 'LAST_UPDATE_DATE') loop

  begin
    execute immediate
    'select 1 from "'||tbl.owner||'"."'||tbl.table_name||'"       where last_update_time = :dt and rownum = 1'     into f
    using to_date('31-dec-08','dd-mon-
yy','nls_date_language=american');

• at least one row found, output the table name dbms_output.put_line('"'||tbl.owner'"."'||tbl.table_name||'"'); exception when no_data_found then
  • ignore the table, it has no data we're after null; end; end loop; end; /

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Jan 15 2009 - 12:52:21 CST