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';
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';
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
I'll give these suggestions a try tomorrow and get back to you both.
Thanks in advance.
ASKER
Sorry for the delay.
I finally tested this today and it is what I needed.
Thanks again.
I finally tested this today and it is what I needed.
Thanks again.
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.tab
end if;
end loop;
end;
I wish it helps.