Using table name from user_tab_columns in a select
Ramky99Aug 18 2010 — edited Aug 19 2010Hi all,
I need to generate a list of tables that contain rows in a certain condition. For the real situation there are hundreds of tables so I've been trying to use all_tab_columns in the query to generate the list of tables to search and then delimit by condition.
e.g.
create table adam1
(
adamname char(10)
);
create table adam2
(
adamname char(10)
);
insert into adam1 values ('ADAM');
insert into adam1 values ('BOB');
insert into adam2 values ('BOB');
...I'd like to see a list of tables that contain 'ADAM' in column 'adamname' so in this instance I'd only see table adam1 in the results.
I've tried using a cursor loop but it doesn't like me using table_name ... do I have to bring this into a variable or some such?
Thanks!
Adam