RE: Dynamic SQL to query all columns on all tables

Just for kicks I am currently trying to parse a view I created using the SQL below.

select

   'select '''||table_name||'.'||column_name||''' col, ' ||column_name||' from ' || table_name ||' union all'||chr(10) x   from all_tab_columns
 where owner=3D'X'
   and data_type in ('CHAR','VARCHAR2')
   and table_name not like 'AQ$_%'
   and table_name not in (select view_name from all_views where owner=3D'X') ;

...

"foo.sql" 17008 lines, 4268377 characters

dba1>@foo.sql

I am betting this doesn't work.=20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Brian Mullin Sent: Tuesday, April 26, 2005 2:57 PM
To: oracle-l_at_freelists.org
Subject: Dynamic SQL to query all columns on all tables

=20

Here's a really bad query, but one I need to figure out.

=20

I want to query all columns in all tables in a schema for a string value. I think I need dynamic SQL for this, has anyone written such a query before?

=20

-Brian

=20

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l