REM Container Description Report
REM Tim Sawmiller 
REM 9 Oct 2002

REM Output file is expected to be in Word Pad format
REM Using these settings:
REM Font Size 9, Landscape, Left 0.25,
REM Right 0.25, Top 0.25, Bottom 0.0

exec jr_context.set_workarea('GLOBAL SHARED WORKAREA');

drop table container_temp_desc_table
/
create table container_temp_desc_table
(table_name varchar2(30) not null,
 table_irid number(38) not null,
 table_ivid number(38) not null,
 column_irid number(38) not null,
 column_ivid number(38) not null,
 key_irid    number(38),
 key_ivid      number(38),
 constraint_reference number(38),
 foreign_table_column_name varchar2(200),
 foreign_table_column_ref number(38),
 key_constraint_type varchar2(10),
 arc_number number(3),
 arc_mandatory varchar2(1))
/

clear buffer
--set echo on
accept v_cont_name prompt 'Enter Container Name: '
accept v_tab_name prompt 'Enter Table to Describe or  for all: '

set termout off



column today new_value _date
column container new_value _container

select to_char(sysdate,'fmMonth DD, YYYY') today from dual;

select &v_cont_name container from dual;

insert into container_temp_desc_table
(table_name,
 table_irid,
 table_ivid,
 COLUMN_IRID,
 COLUMN_IVID,
 KEY_IRID,
 KEY_IVID,
 CONSTRAINT_REFERENCE,
 FOREIGN_TABLE_COLUMN_REF,
 KEY_CONSTRAINT_TYPE)
SELECT /*+ RULE */ tab.name,
       tab.irid,
       tab.ivid,
       col.irid,
       col.ivid,
       keys.irid,
       keys.ivid,
       keys.constraint_reference,
       keys.foreign_column_reference,
       keys.constraint_type
  FROM  ci_key_components keys,
       ci_columns col,
       ci_table_definitions tab,
       ci_folder_members F,
       ci_container_elements cont
WHERE
   upper(cont.name) = upper('&v_cont_name')
      and cont.irid = F.folder_reference
   and cont.ivid = F.parent_ivid
   and F.member_object = tab.irid
      AND tab.name like nvl(upper('&v_tab_name'),tab.name)
   AND tab.irid = col.table_reference
   AND tab.ivid = col.parent_ivid
   and col.table_reference = keys.pac_reference (+)
   and col.parent_ivid = keys.parent_ivid (+)
   and col.irid = keys.column_reference (+)
/ 
commit
/

update container_temp_desc_table tdt
   set foreign_table_column_name = 
       (select col.name
          from ci_columns col
          where column_irid = col.irid
          and column_ivid = col.ivid)
  where tdt.key_constraint_type = 'UNIQUE'
/

commit
/

update container_temp_desc_table tdt
   set (foreign_table_column_name,
        arc_number,
        arc_mandatory) = 
       (select tab.name || '.' || col.name, 
               cons.arc_number,
               cons.arc_mandatory
          from ci_foreign_key_constraints cons,
               ci_table_definitions tab,
               ci_columns col
         where cons.irid        = tdt.constraint_reference
           and tab.irid  = cons.foreign_table_reference
           and col.irid = tdt.foreign_table_column_ref)
  Where tdt.key_constraint_type = 'FOREIGN'
/

commit
/

set pause off
set term off 
set echo off
set linesize 154
SET PAGESize 68

column today new_value _date
select to_char(sysdate, 'fmMonth DD, YYYY') today from dual;

ttitle left _date center _container ' Table Descriptions with Constraints' right 'Page: ' sql.pno skip 3

column tname format A29 wrap heading 'Table Name'
column CNAME FORMAT A30 WRAP HEADING 'Column Name'
column cnull format A8  WRAP heading 'Null?'
column cdtype format A15 WRAP heading 'Type'
column kctype format A10 WRAP heading 'Constraint'
column ftname format A45 WRAP heading 'Derived From'
column arc    format A4 WRAP heading 'Arc'

break on tname skip 1 on cname on cnull on cdtype

spool d6i_container_desc.wri


SELECT rtrim(tdt.table_name) tname,
       rtrim(COL.name) CNAME,
       decode(col.null_indicator,'NULL',null,
              col.null_indicator) cnull,
       decode(COL.datatype,
              'DATE',col.datatype,
              'INTEGER',col.datatype,
              'NUMBER',col.datatype || '(' ||
                       to_char(COL.maximum_length) ||
                       ',' ||
                       to_char(NVL(COL.decimal_places,0)) || ')',
              col.datatype || '(' ||
              to_char(COL.maximum_length) || ')') cdtype,
       rtrim(tdt.KEY_CONSTRAINT_TYPE) KCTYPE, 
       tdt.foreign_table_column_name ftname,
       tdt.arc_number || ' ' || decode(tdt.arc_mandatory,'Y','Mand',null) arc
 FROM  ci_columns col,
       container_temp_desc_table tdt
 WHERE 
 col.irid = tdt.column_irid
 and col.ivid = tdt.column_ivid
 order by tdt.table_name, 
          col.name
/            
           
spool off
set termout on