Re: (?) on querying 'columns'

From: Nick Ife <no.account_at_nt.com>
Date: 1995/10/06
Message-ID: <no.account-061095083320_at_nnsgm831.lon40.nt.com>#1/1


This could be done as a two-stage process. In part 1, a simple query on USER_TAB_COLUMNS will tell you all columns in all tables which are VARCHAR2 and the right length. By concatenating the table_names and column_names together with some text strings which happen to be valid SQL, you can generate a script to do part 2, which finds the string wherever it is. This looks a bit messy, but it works!

select
'select * from '||table_name||' where '||column_name||' like ''%STRING%'';' from user_tab_columns where data_type='VARCHAR2' and data_length> n and table_name in (select table_name from user_tables) /

Output from this query looks like:

select * from TABLE_1 where COLUMN_1 like '%STRING%';
select * from TABLE_1 where COLUMN_2 like '%STRING%';
select * from TABLE_2 where COLUMN_3 like '%STRING%';

Which is the script you run for part 2. If you set the pagesize, feedback etc OK and spool the output to a doit.sql you can run part 2 automatically from the part 1 script.

I hope that makes sense - it's easier to do than to explain!! Nick

In article <DFzux9.1wA_at_onyx.indstate.edu>, arc_at_mama.indstate.edu (Arc Wave) wrote:

> This may sound awkward, but here is what I want to do:
>
> I need to be able to select everything from a table and for all
> VARCHAR2 datatypes that are greater than <n> length to look in that
> column for a specific string. I need to search every column in a table.
> This is not just one table, but about one thousand of them.
>
> Has anyone done this? Or knows of an easy way?
>
> This may be in a FAQ somewhere, if so, where?
>
> I am coming back up to speed on SQL, but remembering old tricks is hard.
>
> Using Oracle 7.x on an HP-UX 9000.
>
> --
> .---------------------------------------------------------------------------.
> | Paul Cardwell | email: arc_at_mama.indstate.edu |
> |________________________.-------------------------.________________________|
> `--http://mama.indstate.edu/users/arc---*---OS/2 WARP / Linux / AmigaCD32---'
Received on Fri Oct 06 1995 - 00:00:00 CET

Original text of this message