Re: Finding tables that contain a certain column value

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Fri, 30 Sep 2005 14:48:26 -0400
Message-ID: <TqydnRfjxqTmFaDeRVn-vg@comcast.com>

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:...
>
> "Raptor" <UseLinkToEmail_at_dbForumz.com> wrote in message
> news:4_895641_c7c219c899b44f2a219e330f225e5482_at_dbforumz.com...
>> I'm currently working in Oracle 8i and I'm attempting to write an SQL
>> statement to provide a listing of tables where the column_name
>> contains a certain value, but I'm not having any luck.
>>
>> I can obtain a listing of all the availabe tables with the column I'm
>> looking for, but I cannot filter the listing further for just the
>> column value.
>>
>> select table_name from all_tab_columns
>> where column_name = 'JCN';
>>
>> I now need to filter the listing further to just those tables that
>> contain the correct value in JCN
>>
>> Any help would be appreciated.
>>
>> --
>> Posted using the http://www.dbforumz.com interface, at author's request
>> Articles individually checked for conformance to usenet standards
>> Topic URL:
>> http://www.dbforumz.com/Oracle-Finding-tables-column-ftopict259881.html
>> Visit Topic URL to contact author (reg. req'd). Report abuse:
>> http://www.dbforumz.com/eform.php?p=895641
>
>
> you'll need to use dynamic SQL (within PL/SQL), since the table names are
> unknown at runtime
>
> you'll end of with something like this (just a sketch, check the PL/SQL
> manual for specific reference
>
> for r1 in (
> select table_name from user_Tables where c
>

ok, let's try that again (again, sample code, not tested, not even looked at a second time)

declare

    v_sql varchar2(1000);
    n_count number;
begin

    for r1 in (

        select table_name from all_tab_columns
        where column_name = 'JCN'

    )
    loop

        v_sql := 'select count(*) from ' || r1.table_name || ' where jcn <some condition>';

• then use dynamic SQL to execute the statement execute immediate v_sql into n_count;
• then do something with the results if n_count > 1 then dbms_output.put_line( r1.table_name); end if; end loop; end;

Received on Fri Sep 30 2005 - 13:48:26 CDT