Re: Looking for a Column Name in 8.05

From: Suresh Bhat <oracleguru_at_lycos.com>
Date: 28 Jan 2003 10:00:42 -0800
Message-ID: <9c64199c.0301281000.3d967d47_at_posting.google.com>

Tim Marshall <tmarshal_at_morgan.ucs.mun.ca> wrote in message news:<3E359561.33F6C07E_at_morgan.ucs.mun.ca>...
> Hi there, I'm trying to find all column names that contain a certain
> text, say "part" in my Oracle tables.
>
> I've tried:
>
> select column_name from all_all_tables where column_name like '%part%'
>
> And this does not work. Any suggestions? Thanks in advance.

Try this UNIVERSAL script for any column. From the date of the script it was obviously written for Oracle Release 6 or 7, but should work for 8 or even 9i.

HTH. Suresh Bhat

set doc off pause off wrap on

/*

   Author      : Suresh N. Bhat
   Date        : 10/27/94      
   File Name   : check_column_name.sql    
   Usage       : On SQL*Plus prompt enter:
                       _at_check_column_name

   Description : Checks if similarly named column exists in other Oracle

                 tables so that we can use consistent column names
	  	 throughout our Databases.

*/

column answer new_value answer  

accept answer char prompt 'Enter Column Name [ dual ]: '  

set termout off  

select nvl( upper('&answer'), 'DUAL') answer   from dual
/

column owner format a16

column today        new_value today   noprint
column time         new_value time    noprint
column db_name      new_value db_name   noprint
 

select value db_name
  from v$parameter
 where name = 'db_name'
/

select to_char(sysdate, 'DD-MON-YYYY') today,

         to_char(sysdate, 'HH:MI:SS AM') time   from dual;  

ttitle today center 'COLUMN NAMES THAT CONTAIN THE WORD' -

   right 'Page ' format 990 sql.pno skip 1 -    time center '&answer' skip 1 -
   check_column_name.sql center &db_name skip 2

set feedback off verify off termout on trimspool on set newpage 0 pagesize 58

spool $HOME/rep/check_column_name_&db_name..lst

select owner, table_name, column_name   from all_tab_columns
 where column_name like upper('%&answer%') union

select   'NO COLUMNS FOUND', '##############################',
	 '##############################'

  from all_tab_columns
 where column_name like upper('%&answer%') having count(*) = 0
/  

prompt
prompt

prompt.                      ********** END OF REPORT *********
 

spool off

exit Received on Tue Jan 28 2003 - 19:00:42 CET