Re: Simple Oracle Script (I think)

"G. Blair" <geoffrey_blair_at_hotmail.com> wrote in message news:ee5bb20d.0310281401.576ccc20_at_posting.google.com...
> Can anyone give help me with the syntax for a script that would loop
> through all_tab_columns looking for tables that have a certain row.
> While doing this, as each table name is provided (through a cursor I
> imagine), I would like to query that table to see if records with a
> certain primary key exist. The result would be a report to the screen
> of the table names and record counts that contain the search criteria
> (the PK I am searching for).
>
> Thanks for the help.

Search this group for similar/other solutions:

Here is one example. Modify it for your purpose:

set serveroutput on size 1000000

declare

   cnt number;
   giv_str varchar(200):='&1';
begin
 dbms_output.put_line (rpad('Table Name',31,' ')||rpad('Column Name',31,' '));
 dbms_output.put_line (rpad('-',29,'-')||' '|| rpad('-',29,'-'));

   for tab_name in (select tname from tab where tabtype='TABLE') loop

       for col_name in (select column_name from user_tab_columns where
           table_name=trim(tab_name.tname) and data_type in ('VARCHAR2', 'VARCHAR', 'CHAR')
           and data_length >= length(giv_str)) loop
           execute immediate  'select count(*)  from '||
trim(tab_name.tname) ||' where '
                 || trim(col_name.column_name) || ' like ''%'||
trim(giv_str) ||'%'''  into cnt;
           if cnt > 0  then
              dbms_output.put_line(rpad(tab_name.tname,31,' ') ||
rpad(col_name.column_name,31,' '));
           else
              null;
           end if;
       end loop;

   end loop;
end;
/

Anurag Received on Tue Oct 28 2003 - 16:15:57 CST