-- Author	- R.Arul kumar
-- Date	- 21-NOV-2001
-- Connect to the user for which tables creation script is required.
Prompt Generating Scripts...
set head off
set feedback off
set pagesize 0
set linesize 32767
set trimspool on
spool all_tables.sql
select 'CREATE TABLE '|| x.TABLE_NAME ||' ( '|| x.COLUMN_NAME ||' '||
DECODE(DATA_TYPE,'NUMBER', 
		DATA_TYPE || '('||NVL(DATA_PRECISION, 22)||','||NVL(DATA_SCALE,0)||') ) '||'TABLESPACE '||y.tablespace_name||' '|| 'PCTUSED '   ||y.pct_used||' '|| 'PCTFREE '   ||
	y.pct_free||' '|| 'STORAGE ( '|| 'INITIAL '||y.initial_extent||' '|| 'NEXT '||
	y.next_extent||' '|| 'MINEXTENTS '||y.min_extents||' '|| 'MAXEXTENTS '||y.max_extents||' ) ;',
		'VARCHAR2',DATA_TYPE || '('||DATA_LENGTH||') ) '|| 'TABLESPACE '||y.tablespace_name||' '|| 'PCTUSED '   ||y.pct_used||' '|| 'PCTFREE '   ||
	y.pct_free||' '|| 'STORAGE ( '|| 'INITIAL '||y.initial_extent||' '|| 'NEXT '||
	y.next_extent||' '|| 'MINEXTENTS '||y.min_extents||' '|| 'MAXEXTENTS '||y.max_extents||' ) ;', 
		'DATE', DATA_TYPE ||' ) '||  'TABLESPACE '||y.tablespace_name||' '|| 'PCTUSED '   ||y.pct_used||' '|| 'PCTFREE '   ||
	y.pct_free||' '|| 'STORAGE ( '|| 'INITIAL '||y.initial_extent||' '|| 'NEXT '||
	y.next_extent||' '|| 'MINEXTENTS '||y.min_extents||' '|| 'MAXEXTENTS '||y.max_extents||' ) ;' ,	 
		DATA_TYPE||' ) '|| 'TABLESPACE '||y.tablespace_name||' '|| 'PCTUSED '   ||y.pct_used||' '|| 'PCTFREE '   ||
	y.pct_free||' '|| 'STORAGE ( '|| 'INITIAL '||y.initial_extent||' '|| 'NEXT '||
	y.next_extent||' '|| 'MINEXTENTS '||y.min_extents||' '|| 'MAXEXTENTS '||y.max_extents||' ) ;') 
from user_Tab_columns x , user_tables y
where x.table_name = y.table_name
and x.table_name in (select table_name from user_tab_columns group by table_name having count(TABLE_NAME)=1)
union all
select  DECODE ( to_char(a.column_id), '1',   
'CREATE  TABLE ' || a.table_name|| ' ( '|| a.column_name ||' '||
	DECODE(DATA_TYPE,'NUMBER', 
		DATA_TYPE || '('||NVL(DATA_PRECISION, 22)||','||NVL(DATA_SCALE,0)||') , ',
		'VARCHAR2',DATA_TYPE || '('||DATA_LENGTH||') , ', 
		'DATE', DATA_TYPE ||'  , ' ,	 
		DATA_TYPE||'  , ') ,
(SELECT MAX(column_id) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = A.TABLE_NAME),  
RPAD(A.column_name,35,' ')  ||' '||
	DECODE(DATA_TYPE,'NUMBER', 
		DATA_TYPE || '('||NVL(DATA_PRECISION, 22)||','||NVL(DATA_SCALE,0)||') ) '|| 'TABLESPACE '||b.tablespace_name||' '|| 'PCTUSED '   ||b.pct_used||' '|| 'PCTFREE '   ||
	b.pct_free||' '|| 'STORAGE ( '|| 'INITIAL '||b.initial_extent||' '|| 'NEXT '||
	b.next_extent||' '|| 'MINEXTENTS '||b.min_extents||' '|| 'MAXEXTENTS '||b.max_extents||' ) ;',
		'VARCHAR2',DATA_TYPE || '('||DATA_LENGTH||') ) '|| 'TABLESPACE '||b.tablespace_name||' '|| 'PCTUSED '   ||b.pct_used||' '|| 'PCTFREE '   ||
	b.pct_free||' '|| 'STORAGE ( '|| 'INITIAL '||b.initial_extent||' '|| 'NEXT '||
	b.next_extent||' '|| 'MINEXTENTS '||b.min_extents||' '|| 'MAXEXTENTS '||b.max_extents||' ) ;', 
		'DATE', DATA_TYPE ||' ) '||  'TABLESPACE '||b.tablespace_name||' '|| 'PCTUSED '   ||b.pct_used||' '|| 'PCTFREE '   ||
	b.pct_free||' '|| 'STORAGE ( '|| 'INITIAL '||b.initial_extent||' '|| 'NEXT '||
	b.next_extent||' '|| 'MINEXTENTS '||b.min_extents||' '|| 'MAXEXTENTS '||b.max_extents||' ) ;' ,	 
		DATA_TYPE||' ) '||'TABLESPACE '||b.tablespace_name||' '|| 'PCTUSED '   ||b.pct_used||' '|| 'PCTFREE '   ||
	b.pct_free||' '|| 'STORAGE ( '|| 'INITIAL '||b.initial_extent||' '|| 'NEXT '||
	b.next_extent||' '|| 'MINEXTENTS '||b.min_extents||' '|| 'MAXEXTENTS '||b.max_extents||' ) ;') ,
RPAD(A.column_name,35,' ')  ||' '||
 DECODE(DATA_TYPE,'NUMBER', 
		DATA_TYPE || '('||NVL(DATA_PRECISION, 22)||','||NVL(DATA_SCALE,0)||') , ',
		'VARCHAR2',DATA_TYPE || '('||DATA_LENGTH||') , ', 
		'DATE', DATA_TYPE ||' , ' ,	 
		DATA_TYPE||' , ') )
FROM USER_TAB_COLUMNS A , USER_TABLES B
where A.Table_Name= B.Table_Name
and  A.TABLE_NAME IN ( select TABLE_NAME from user_TAB_columns 
group by TABLE_NAME having count(TABLE_NAME) > 1 )
/
spool off;
prompt Script Generation Over!
prompt Chk in All_Tables.sql!!
set head on
set pagesize 20
set linesize 80
set feedback on