spool show_depends.rslt 

set echo off;
set feedback off;
col val0 		new_val 	x_dual 	noprint

select upper('&&TABLE_NAME') val0 from dual;

set heading off termout off echo off verify off;
set linesize 77;
clear col;
clear breaks;
clear compute;
set pause off;
ttitle off;

/* ******************************************************************* */
/*                                                                     */
/*  Name:    show_deps.sql                                             */
/*  Creator: Rick Kupcunas-Technology Solutions Company                */
/*  Created: 1999-Dec-07                                               */
/*                                                                     */
/*  This script will show all objects associated with a table          */
/*                                                                     */
/* ******************************************************************* */

set verify off
PROMPT
PROMPT name of object:
ACCEPT name
PROMPT

set termout off
drop table all_dep_tmp;
create table all_dep_tmp
AS 
select * 
from user_dependencies;
set termout on


PROMPT objects referencing &name 
PROMPT ===================

SELECT level,
            SUBSTR(LPAD(' ',2*(LEVEL-1))||name,1,50) object 
 FROM all_dep_tmp
START WITH referenced_name = UPPER('&name')
CONNECT BY PRIOR name = referenced_name ;

PROMPT objects being referenced by &name
PROMPT ===========================

SELECT level,
        SUBSTR(LPAD(' ',2*(LEVEL-1))||referenced_name,1,50) object 
 FROM all_dep_tmp
START WITH name = UPPER('&name')
CONNECT BY PRIOR referenced_name = name ;

set termout off
drop table all_dep_tmp;
set termout on
set verify on









