DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: revserse tables script

  1. #1
    Join Date
    Nov 2000
    Posts
    175

    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,

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492
    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

  3. #3
    Join Date
    Nov 2000
    Posts
    175
    THANKS, Worked Great!

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Greater than the "great" it was before?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width