/*
 * Title  : sho_dat_dic.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 29-Jul-2017
 * Release: 1.0.0
 * Purpose: This script shows data dictionary.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on
set termout on

set linesize 250
set pagesize 0
set pause off

set heading on
set headsep on
set underline on
set colsep " "

set define on
set verify off
set escape off
set embedded on

col table_name	heading	"Table Name"		format a30
col tab_comment	heading "Table Comments"	format a50 
col column_name	heading	"Column Name"		format a30
col data_type	heading "Data Type"		format a12
col data_length heading	"Data Size"		format 999999999
col nullable	heading	"Null?"			format a5
col cons_type	heading	"Constraint Type"	format a15
col ref_table	heading	"Parent Table"		format a30
col col_comment	heading "Column Comments"	format a50

break on table_name on tab_comment on column_name on data_type on data_length on nullable

accept p_owner	prompt 'Enter Owner: '

spool &&rpt_dir/sho_dat_dic.rpt

select
	a.table_name,
	d.comments as tab_comment,
	a.column_name,
	a.data_type,
	a.data_length,
	a.nullable,
	smpl_misc.get_cons_type('&&p_owner',b.constraint_name) cons_type,
	(select owner||'.'||table_name from dba_constraints where owner=b.owner and constraint_name=b.r_constraint_name) ref_table,
	c.comments as col_comment	
from
	dba_tab_columns a,
	(select
		x.owner,
		x.table_name,
		x.r_constraint_name,
		y.column_name,
		y.constraint_name
	from
		dba_constraints x,
		dba_cons_columns y
	where
		x.owner=y.owner
	and	x.constraint_name=y.constraint_name
	) b,
	dba_col_comments c,
	dba_tab_comments d
where
	a.owner=b.owner(+)
and	a.table_name=b.table_name(+)
and	a.column_name=b.column_name(+)
and	a.owner=c.owner
and	a.table_name=c.table_name
and	a.column_name=c.column_name
and	a.owner=d.owner
and	a.table_name=d.table_name
and	a.owner=upper('&&p_owner')
order by
	a.table_name,
	a.column_id;

spool off

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80

set termout on
set serverout off
set feedback on
set echo off
