revserse tables script

Hi,

I got a great script off the web to reverse an entire schema. All of the other portions of the script works great. The only problem is with the tables portion.

Here is the code:
SELECT '
rem /*
rem * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem * CREATE tables, primary keys, storage, and pct parameters
rem * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem */'
FROM DUAL;

CLEAR COLUMN;
COLUMN table_name NOPRINT;
COLUMN seq NOPRINT;
BREAK ON table_name SKIP 1;

SELECT table_name, 0 seq, 'CREATE TABLE ' || lower ( table_name ) || ' ( '
FROM user_tables
UNION
SELECT a.table_name, column_id seq, ' ' || RPAD( LOWER( column_name ),35,' ') || ' ' ||
RPAD ( DECODE ( data_type, 'NUMBER', data_type || '(' ||
data_precision || ',' || data_scale || ') ' ,
'DATE' , data_type || ' ',
'LONG' , data_type || ' ',
data_type || '(' || data_length || ') ' ), 20, ' ') ||
DECODE ( NULLABLE, 'N', ' NOT NULL ', NULL ) ||
decode ( column_id, total_columns, ')', ',' )
FROM user_tab_columns a, user_tables b,
( SELECT table_name, COUNT(1) total_columns
FROM user_tab_columns
GROUP BY table_name ) c
WHERE a.table_name = b.table_name
AND a.table_name = c.table_name
AND data_type <> 'UNDEFINED'
UNION
SELECT table_name, 8000 seq, ' PRIMARY KEY ' ||
DECODE ( SUBSTR (constraint_name,1,3), 'SYS', NULL, LOWER ( constraint_name) )
|| ' ( '
FROM user_constraints
WHERE constraint_type IN ('P')
UNION

SELECT a.table_name, (8000 + POSITION) seq, ' ' ||
lower (column_name) || DECODE (position, total_cons, ' );', ',')
FROM user_cons_columns a, user_constraints b,
( SELECT a.constraint_name, count(a.constraint_name) total_cons
FROM user_cons_columns a, user_constraints b
WHERE a.constraint_name = b.constraint_name
AND b.constraint_type IN ('P')
GROUP by a.constraint_name ) c
WHERE a.constraint_name = b.constraint_name
AND a.constraint_name = c.constraint_name
AND constraint_type IN ('P')
UNION
SELECT table_name, 9998 seq, ' PCTFREE ' ||
PCT_FREE || ' PCTUSED ' || PCT_USED
FROM user_tables
UNION
SELECT table_name, 9999 seq, ' STORAGE ( INITIAL ' ||
INITIAL_EXTENT || ' NEXT ' || NEXT_EXTENT || ' );'
FROM user_tables
ORDER BY table_name, seq;


Here is the OUTPUT: As you can see this doesn't look like a complete create table statement.
rem /*
rem * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem * CREATE tables, primary keys, storage, and pct parameters
rem * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem */


case_number,
associated_case );
PCTFREE 10 PCTUSED 40
STORAGE ( INITIAL 131072 NEXT 131072 );

case_number,
comment_sequence );
PCTFREE 10 PCTUSED 40
STORAGE ( INITIAL 131072 NEXT 131072 );

case_number,
event_sequence );
PCTFREE 10 PCTUSED 40
STORAGE ( INITIAL 131072 NEXT 131072 );


Are there any sql gurus that could look at this. I am having trouble understanding the code to see where its going wrong.

Thanks,

Try this one:

SELECT '
rem /*
rem * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem * CREATE tables, primary keys, storage, and pct parameters
rem * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem */'
FROM DUAL;

CLEAR COLUMN;
COLUMN table_name NOPRINT;
COLUMN seq NOPRINT;
BREAK ON table_name SKIP 1;
SET HEAD OFF PAGES 0 RECSEP OFF FEED OFF

SELECT table_name, 0 seq, 'CREATE TABLE ' || lower ( table_name ) || ' ( ' txt
FROM user_tables
UNION
SELECT a.table_name, column_id seq, ' '
|| RPAD( LOWER( column_name ),35,' ') || ' '
|| RPAD( DECODE ( data_type, 'NUMBER', data_type || '(' ||
data_precision || ',' || data_scale || ') ' ,
'DATE' , data_type || ' ',
'LONG' , data_type || ' ',
data_type || '(' || data_length || ') ' ), 20, ' ') ||
DECODE ( NULLABLE, 'N', ' NOT NULL ', NULL ) ||
decode ( column_id, total_columns, ')', ',' )
FROM user_tab_columns a, user_tables b,
( SELECT table_name, COUNT(1) total_columns
FROM user_tab_columns
GROUP BY table_name ) c
WHERE a.table_name = b.table_name
AND a.table_name = c.table_name
AND data_type <> 'UNDEFINED'
UNION
SELECT table_name, 8000 seq, ' PRIMARY KEY ' ||
DECODE ( SUBSTR (constraint_name,1,3), 'SYS', NULL,
LOWER (constraint_name) )|| ' ( '
FROM user_constraints
WHERE constraint_type IN ('P')
UNION
SELECT a.table_name, (8000 + POSITION) seq, ' ' ||
lower (column_name) || DECODE (position, total_cons, ' );', ',')
FROM user_cons_columns a, user_constraints b,
( SELECT a.constraint_name, count(a.constraint_name) total_cons
FROM user_cons_columns a, user_constraints b
WHERE a.constraint_name = b.constraint_name
AND b.constraint_type IN ('P')
GROUP by a.constraint_name ) c
WHERE a.constraint_name = b.constraint_name
AND a.constraint_name = c.constraint_name
AND constraint_type IN ('P')
UNION
SELECT table_name, 9998 seq, ' PCTFREE ' ||
PCT_FREE || ' PCTUSED ' || PCT_USED
FROM user_tables
UNION
SELECT table_name, 9999 seq, ' STORAGE( INITIAL ' ||
INITIAL_EXTENT || ' NEXT ' || NEXT_EXTENT || ' );'
FROM user_tables
ORDER BY table_name, seq

THANKS, Worked Great!

Greater than the "great" it was before?