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

From: Homer <HomerS007_at_gmail.com>
Date: Thu, 15 Jan 2009 11:05:22 -0800 (PST)
Message-ID: <ccc9b1fd-f3ae-46e2-a04b-00ef3af8db38_at_g39g2000pri.googlegroups.com>

On Jan 15, 10:54 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> 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. :)- Hide quoted text -
>
> - Show quoted text -

Thanks for your help, Vladimir. Received on Thu Jan 15 2009 - 13:05:22 CST