ckcol.sql

REM ***************************************************
REM Author : Matt Sherek
REM This utility will display all tables where the
REM column in used.
REM ***************************************************
REM set echo off;
REM set verify off;

set pagesize 60
set linesize 144

COLUMN OWNER				HEADING "OWNER"	      FORMAT A8
COLUMN TABLE_NAME       ALIAS tab	HEADING "TABLE"	      FORMAT A20
COLUMN COLUMN_NAME       HEADING "COLUMN"      FORMAT A18
COLUMN data_type       HEADING "DATATYPE"      
COLUMN DATA_LEN       HEADING "Data Length" FORMAT A8
COLUMN NULLABLE       HEADING "Null?"     FORMAT A5


PROMPT
PROMPT **** CHECK COLUMN(s) IN TABLE(S) ****
PROMPT
PROMPT Column pattern can be %CUST%, %TX, ORDR%, or simply the name 
PROMPT
ACCEPT  tcolname   CHAR PROMPT "Enter Column Name/Pattern to search for: "
ACCEPT  towner   CHAR PROMPT "Enter Owner: "

SELECT  owner,
	table_name	   tab,
	column_name,
	data_type,
	decode(data_type,'NUMBER','('|| DATA_PRECISION||','||DATA_SCALE||')',
               data_length) DATA_LEN,
	NULLABLE
  FROM  ALL_TAB_COLUMNS
  WHERE owner LIKE UPPER('&&towner') 
    AND column_name LIKE UPPER('&&tcolname')
	ORDER BY owner, table_name 
;