Sunday, April 29, 2012

Print Oracle Table Definitions


HOSTNAME::ORACLE $ cat dd_step4b.sql
rem Filename : dd_step4b.sql
rem Note     : This Version is for IFMS and prints only the
rem          : 'Data' Tables, which begin with FMS_TD_
rem
SELECT name FROM V$DATABASE;
SHOW USER;
spool dd_step4b.op
select TO_CHAR(SysDate,'DD/Mon/YYYY HH:MI P.M.') from dual;
rem
rem set heading off;
rem
set termout  on
set feedback OFF
set verify off
set pagesize 59
set linesize 132
set newpage  0
set space    1
set recsep off
set arraysize 5
rem
rem column COL_COMMENT format a40  word_wrap
column TABLE_NAME  format a30 HEADING   'TABLE NAME'
column KEY_SEQ     format 99  heading  'KEY'
column COLUMN_ID   format 999  heading  'ID'
column COLUMN_NAME format a30  heading  'FIELD NAME'
column TYPE_LENGTH format a15   heading  'DATA TYPE'
rem column CODED_TYPE  format a8   heading  'LENGTH'
rem column DATA_SCALE  format 9    heading  'DEC'                     
column LENGTH      format 9999 heading  'LENGTH'
column NULLABLE    format a5   heading  'NULL?'                
rem
column TODAY       noprint   new_value   date_var
column USER        noprint   new_value   user_var              
rem
break on TABLE_NAME SKIP 2 
rem
ttitle left 'Date: ' 30th. April 2000 -
center 'IFMS - Tables and Columns  in the FMSPRD1  Database' -
  skip 2
rem  right  'Page ' format 99  SQL.PNO -
rem  skip 2
rem
rem spool chk_table_defs.op
rem
SELECT   table_name,key_seq
        ,column_id,column_name
        ,data_type || '(' || TO_CHAR(length) || ')'  type_length
        ,data_scale
        ,nullable
FROM     barrys_dd2x  
WHERE    table_name LIKE 'FMS_TD_%'
ORDER BY table_name, key_seq,column_id;
rem
set termout on
rem
spool off;
quit;
HOSTNAME::ORACLE $