table.sql

REM **************************************************************************
REM CT.SQL
REM ------
REM Generate CREATE TABLE statements using Catalog tables
REM
REM Mike Woodhouse 1991
REM
REM Usage:
REM
REM @CT tablename outputfilename
REM
REM tablename: Any table name (standard SQL wildcards accepted)
REM outputfilename: Target SQL file name (no extension, .SQL supplied)
REM **************************************************************************

SET ECHO OFF
SET TIMING OFF
SET FEEDBACK OFF

SET VERIFY OFF
SET TERMOUT OFF
SET PAGES 999
SET HEADING OFF

COL tabname NOPRINT
COL seqno   NOPRINT
COL seq2    NOPRINT

SELECT
  table_name tabname
, 1 seqno
, 0 seq2
, 'CREATE TABLE '||LOWER(table_name)||' (' txt
FROM
  user_tables
WHERE table_name LIKE UPPER('&1')
UNION
SELECT
  table_name tabname
, 2 seqno
, column_id seq2
, DECODE(column_id
        ,1,' '
          ,',')||column_name||' '||
  DECODE(data_type
        ,'NUMBER','NUMBER '||
             DECODE(data_precision
                   ,NULL,''
                        ,DECODE(data_scale
                               ,NULL,'('||data_precision||')'
                                    ,'('||data_precision||','||data_scale||')'
                               )
                   )
        ,'CHAR'  ,'CHAR ('||data_length||')'
        ,'DATE'  ,'DATE'
        ,'LONG'  ,'LONG')||' '||
  DECODE(nullable
        ,'Y',''
            ,'NOT NULL') txt
FROM
  user_tab_columns
WHERE table_name LIKE UPPER('&1')
UNION
SELECT
  table_name
, 3 seqno
, 0 seq2
, ')' txt
FROM
  user_tables
WHERE table_name LIKE UPPER('&1')
UNION
SELECT
  segment_name
, 4 seqno
, 0 seq2
, ' TABLESPACE '||tablespace_name
FROM
  user_segments
WHERE segment_name LIKE UPPER('&1')
AND   segment_type = 'TABLE'
UNION
SELECT
  table_name
, 5 seqno
, 0 seq2
, ' PCTFREE '||pct_free
FROM
  user_tables
WHERE table_name LIKE UPPER('&1')
UNION
SELECT
  s.segment_name
, 6 seqno
, 0 seq2
, ' STORAGE (INITIAL '||SUM(s.blocks)*2048||
  ' NEXT '||t.next_extent||
  ' MAXEXTENTS '||t.max_extents||
  ' PCTINCREASE 0);'
FROM
  user_segments s
, user_tables t
WHERE s.segment_name LIKE UPPER('&1')
AND   s.segment_name = t.table_name
AND   t.table_name LIKE UPPER('&1')
AND   s.segment_type = 'TABLE'
GROUP BY s.segment_name,t.next_extent,t.max_extents
ORDER BY 1,2,3

SPOOL &2..sql
/
SPOOL OFF

SET ECHO ON
SET TIMING ON
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON
SET HEADING ON