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:54:14 -0800 (PST)
Message-ID: <d4b98c45-8841-4ea0-b908-5c597bc69929_at_g39g2000pri.googlegroups.com>

On Jan 15, 9:52 pm, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> 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

And scrap that
 c cursor;
line, it has no purpose, just a leftover from a different test case. :) Received on Thu Jan 15 2009 - 12:54:14 CST