Link to home
Start Free TrialLog in
Avatar of troesler
troesler

asked on

Find tables with a specific column name that contains a specific value

Is it possible to do the following?

I'd like to search a database for tables that contain a column named "TRANS_SEQ_NBR" containing the value "6103052".

I know to get the table names I just do the following, but I have no idea I would incorporate a search for a specific value within the resulting tables.  Perhaps a PL/SQL block/loop?

SELECT table_name FROM all_tab_columns WHERE column_name = 'TRANS_SEQ_NBR';
Avatar of prast1007
prast1007

You can do it by creating a PL/SQL procedure, and your sql statement is used for the cursor.

Basically you can do this
set serveroutput on
Declare
   cursor crsData is
               SELECT table_name
                  FROM all_tab_columns
                WHERE column_name = 'TRANS_SEQ_NBR';

  nmbCount   number;
begin
   for i in crsdata loop
        execute immediate 'Select count(rowid) into :nmbCount from '||i.table_name||' where 'TRANS_SEQ_NBR = 6103052 ' into nmbCount;
          if nmbCount > 0 then -- the record exists
             dbms_output.put_line(i.table_name);
          end if;
   end loop;
end;
               

I wish it helps.
ASKER CERTIFIED SOLUTION
Avatar of prast1007
prast1007

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of troesler

ASKER

This is for a very INfrequent search of the database, so performance is not an issue.
I'll give these suggestions a try tomorrow and get back to you both.
Thanks in advance.
Sorry for the delay.
I finally tested this today and it is what I needed.
Thanks again.