-- This script will create a SQL statement to create any tables in the
-- current schema. It will include check constraints but not indices.
-- Mark Reineck 12-19-1997
-- The purpose of these lines is to be sure that there are no blank lines
-- in the output that would prevent the script from running.
-- You must be sure that the width of data_default is big enough for
-- any default values that may be found.

set feedback off
set head off
set echo off
set recsep off
set pages 50000
column data_default format a60
column crt format a45
column colspc format a40
set lines 190

-- Write the script to a file, otherwise it is useless

spool mktables.sql

-- Create the table creation sql statements

select decode(column_id,1,'create table '||t.table_name||' (') as crt,
column_name||' '||data_type||decode(data_type,'DATE',null,'NUMBER',decode(data_scale,0,'('||data_precision||')','('||data_precision||','||data_scale||')'),'('||data_length||')') as colspc,
decode(default_length,null,null,'default '), data_default,
decode(column_id,columns, ') pctfree ' || pct_free || ' pctused ' || pct_used || ' initrans ' || ini_trans || ' maxtrans ' || max_trans || ' tablespace ' || tablespace_name || ' storage (initial ' || initial_extent || ' next ' || next_extent || ' minextents ' || min_extents || ' maxextents ' || max_extents || ' pctincrease ' || pct_increase || ');' ,',') as tblspc
from user_tab_columns c, user_tables t,
(select table_name, count(*) as columns from user_tab_columns
group by table_name) v
where c.table_name=t.table_name and c.table_name=v.table_name
order by t.table_name, column_id;

-- Write out the sql statements to build constraints

select 'alter table '||table_name||' add check (',search_condition,');'
from user_constraints where constraint_type='C';

spool off;

-- Reset some stuff

set feedback on;
set head on;
set echo on;