Category : Databases and related files
Archive   : UTIL91.ZIP
Filename : COLUSED.SQL

rem COLUSED.SQL - This SQL*Plus report shows all tables in the database
rem where a specific column is used.
rem You must have DBA privilege to run this report and
rem must have the DBA synonyms created in your account.
rem
rem Syntax:
rem $ SQLPLUS-S USERNAME/PASSWORD @COLUSED OWNER_NAME COLUMN_NAME
rem
rem Set up the SQL*Plus controls
rem
set echo off
set termout off
set feedback off
set verify off
rem
define ownername = "&1"
define columnname = "&2"
btitle skip 3 center &ownername " Account"
rem
rem Set up the standard DBA report heading
rem
start title80 "Columns Where-Used Report"
rem
set linesize 80
set newpage 0
set numwidth 3
set pagesize 58
rem
spool colused.lis
rem
rem Set up the column formats
rem
column table_name format a24 heading "Table Name" justify center
column column_name format a24 heading "Column Name" justify center
column data_type format a6 heading "Column|Type" justify center
column data_length format 999 heading "Width" justify center
column data_precision format a7 heading "Decimal|Places"JUSTIFY center
column nullable format a7 heading "Nulls|Allowed" justify center
rem
rem SQL statements
rem
select table_name, column_name, data_type, data_length,
lpad(to_char(data_precision),4) data_precision,
lpad(decode(nullable,'Not Null','N','Y'),4) nullable
from dba_tab_columns
where owner like upper('&ownername')
and column_name like upper('&columnname')
order by table_name;
spool off
exit