RE: Dynamic SQL to query all columns on all tables

Well that crashed my database, however I was able to easily union 1000 columns on a small db together and then perform a search. This DB is only 2GB, 9204 with about a 150MB SGA. Of course this thrashed the db but not a bad solution I think if I was just needed to play around and find something. You could easily break this up into a small set of views and then ?? union them together ?? ok, enough bad advice for now. Have fun.

set head off feed off trims on lines 200 term off

spool foovu.sql

select 'create or replace view foovu as ('||chr(10) x from dual union all
select

   'select '''||table_name||'.'||column_name||''' col, ' ||column_name||' str from ' || table_name ||' union all'||chr(10) x   from all_tab_columns
 where owner=3D'DORIS'
   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'DORIS')

   and rownum < 1000
union all
select 'select null col, null x from dual' x from dual union all
select ');'||chr(10) x from dual;

spool off

set head on feed on term on

set sqlbl on
@foovu.sql

=3D=3D=3D=3D THEN RUN =3D=3D=3D=3D dba1>@foo

View created.

dba1>select count(*) from foovu where str like 'AA%';

  COUNT(*)

      3710

1 row selected.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Post, Ethan Sent: Tuesday, April 26, 2005 3:24 PM
To: bmullin_at_salesforce.com; oracle-l_at_freelists.org Subject: RE: Dynamic SQL to query all columns on all tables

...

"foo.sql" 17008 lines, 4268377 characters

dba1>@foo.sql

I am betting this doesn't work.=3D20

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