schema.sql

rem /*
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  * Program : schema.sql
rem  *
rem  * This program will generete schema from database
rem  *
rem  * The script will Generates :
rem  *   1. Drop all objects for user 
rem  *   2. Grant roles from system to user
rem  *   3. Create tables, primary keys and primay indexes, storage and pct paramters 
rem  *   4. Alter tables to create foreign key contraints 
rem  *   5. Create Indexes and unique indexes 
rem  *   6. Create synonyms 
rem  *   7. Create views 
rem  *   8. Create packages and procedures 
rem  *   9. Grant Table privilages to other users
rem  * 
rem  * The script will NOT generate following: 
rem  *   1. Clusters 
rem  *   2. Snapshots 
rem  *   3. Types and Methods 
rem  *   4. #hash indexes, binary indexes , etc 
rem  *   5. partitioned objects, tables, indexes etc. 
rem  *   6. All objects will be created on default tablespace 
rem  *
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  *
rem  * 01/23/99 : John Doe : Program Created
rem  *            smithj@uswest.net
rem  *
rem  */

SET ECHO      OFF
SET TERM      ON
SET VERIFY    OFF

PROMPT *- - - - - - - - - - - - - - - - - - - - - - - - -*
PROMPT *- - -   S C H E M A    G E N E R A T O R    - - -*
PROMPT *- - - - - - - - - - - - - - - - - - - - - - - - -*
PROMPT *                                                 *
PROMPT * This program will generate schema from database *
PROMPT * Please enter the required input >               *
PROMPT *- - - - - - - - - - - - - - - - - - - - - - - - -*
ACCEPT schuser   PROMPT 'Enter user name: '
ACCEPT schuserpw PROMPT 'Enter user password: ' HIDE
ACCEPT constr    PROMPT 'Enter service name: '
ACCEPT syspw     PROMPT 'Enter password for system: ' HIDE
PROMPT *- - - - - - - - - - - - - - - - - - - - - - - - -*
PROMPT * This will take some time, please be patient     *
PROMPT *- - - - - - - - - - - - - - - - - - - - - - - - -*
PROMPT
PROMPT Generating ...

CONNECT &schuser/&schuserpw@&constr;

SET TRIMOUT   ON
SET TRIMSPOOL ON
SET HEADING   OFF
SET FEEDBACK  OFF
SET PAUSE     OFF
SET PAGESIZE  999
SET LINESIZE  80
SET ECHO      OFF
SET TERM      OFF
SET VERIFY    OFF
CLEAR COLUMN;

SPOOL schema.sql

rem /* --  Create Header  --------------------------------------------------------- */


SELECT 'rem /*
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  * Program : sch_' || user || '.sql
rem  *
rem  * This program generate SCHEMA for ' || user || ' directly from database 
rem  *
rem  * ' || to_char(sysdate,'DD-MON-YYYY') || ' : John Doe : Program Auto Generated from DB
rem  *
rem  */'
 FROM DUAL;

SELECT '
rem /*
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  * Config
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  */'
 FROM DUAL;

SELECT '
    SET TRIMOUT   ON
    SET TRIMSPOOL ON
    SET HEADING   OFF
    SET FEEDBACK  ON
    SET PAUSE     OFF
    SET PAGESIZE  999
    SET LINESIZE  80
    SET TERM ON 
    SET ECHO ON '
 FROM DUAL;

rem /* ---------------------------------------------------------------------------- */

SELECT '
rem /*
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  * DROP all objects
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  */'
  FROM DUAL;

COLUMN object_name FORMAT A50

SELECT 'DROP ' || RPAD( object_type,15, ' ') || ' ' ||  LOWER ( object_name ) || ';' 
  FROM user_objects
 ORDER BY object_type, object_name;

/* ---------------------------------------------------------------------------- */

SELECT '
rem /*
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  * ROLE GRANTED by system to user
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  */'
 FROM DUAL;

SELECT ' 
CONNECT system/' || '&syspw' || '@' || '&constr'
  FROM DUAL;

COLUMN seq       NOPRINT;

SELECT ROWNUM seq,  'GRANT ' || RPAD ( LOWER( granted_role ),35,' ') || ' TO ' ||
                    lower ( username ) || 
                    DECODE ( admin_option, 'YES', ' WITH ADMIN OPTION',null) || ';'
  FROM user_role_privs 
 ORDER BY seq;

SELECT ' 
CONNECT ' || '&schuser' || '/' || '&schuserpw' || '@' || '&constr'
  FROM DUAL;

/* ---------------------------------------------------------------------------- */

SELECT '
rem /*
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  * ALTER User for tablespaces
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  */'
 FROM DUAL;

COLUMN seq          NOPRINT;

SELECT 1 seq, 'ALTER USER ' || lower ( username )
  FROM user_users
 UNION
SELECT 2 seq, '      DEFAULT TABLESPACE   ' || lower ( default_tablespace )
  FROM user_users
 UNION
SELECT 3 seq, '      TEMPORARY TABLESPACE ' || lower ( temporary_tablespace ) || ');'
  FROM user_users
 ORDER BY seq;

/* ---------------------------------------------------------------------------- */

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;

/* ---------------------------------------------------------------------------- */

SELECT '
rem /*
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  * ALTER tables for foreign key constraint
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  */'
 FROM DUAL;

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

SELECT table_name, constraint_name,  -1   seq, 'ALTER TABLE ' || LOWER ( table_name )
  FROM user_constraints
 WHERE constraint_type IN ('R')
 UNION
SELECT table_name, constraint_name,  0     seq,  '    ADD CONSTRAINT ' || LOWER ( constraint_name)
  FROM user_constraints
 WHERE constraint_type IN ('R')
 UNION
SELECT a.table_name, a.constraint_name, position seq,  DECODE(position, 1, '       FOREIGN KEY ( ', 
                                                                           '                     ') ||  
                                                       LOWER (a.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 = 'R' 
                                                  GROUP BY a.constraint_name ) c
 WHERE a.constraint_name = b.constraint_name
   AND b.constraint_type IN ('R')
   AND a.constraint_name = c.constraint_name
 UNION
SELECT a.table_name, a.constraint_name, 8000 seq, '       REFERENCES  ' || LOWER ( b.table_name ) || ' ('
  FROM user_constraints a, user_constraints b
 WHERE a.R_CONSTRAINT_NAME = b.constraint_name
   AND a.constraint_type = 'R' 
   AND b.constraint_type = 'P' 
 UNION
SELECT a.table_name, a.constraint_name, (8001 + position) seq,  '                     ' ||  
                                                       LOWER (a.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 = 'R' 
                                                  GROUP BY a.constraint_name ) c
 WHERE a.constraint_name = b.constraint_name
   AND b.constraint_type IN ('R')
   AND a.constraint_name = c.constraint_name
 ORDER BY table_name, constraint_name, seq;


/* ---------------------------------------------------------------------------- */

SELECT '
rem /*
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  * CREATE Indexes, unique indexes, pct parameters
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  */'
 FROM DUAL;

COLUMN index_name NOPRINT;
COLUMN seq        NOPRINT;


SELECT index_name, 0    seq, 'CREATE ' || DECODE(uniqueness, 'UNIQUE', uniqueness,null )  || ' INDEX '
                             ||  LOWER ( index_name ) || ' ON ' || LOWER ( table_name )
  FROM user_indexes
 WHERE table_type = 'TABLE'
   AND SUBSTR(index_name,1,3) <> 'SYS'
 UNION
SELECT a.index_name, column_position seq, DECODE (column_position, 1,'    (' ,'     ') ||  
                                          LOWER( column_name ) ||
                                          decode ( column_position, total_columns, ')', ',' )
  FROM user_ind_columns a, user_indexes b, 
                                          ( SELECT index_name, count(1) total_columns
                                              FROM user_ind_columns
                                             GROUP BY index_name ) c
 WHERE a.table_name = b.table_name
   AND a.index_name = c.index_name
   AND b.table_type = 'TABLE'
   AND SUBSTR(a.index_name,1,3) <> 'SYS'
 UNION
SELECT index_name, 9998 seq, '    PCTFREE ' || PCT_FREE  
  FROM user_indexes
 WHERE table_type = 'TABLE'
   AND SUBSTR(index_name,1,3) <> 'SYS'
 UNION
SELECT index_name, 9999 seq, '    STORAGE ( INITIAL ' || INITIAL_EXTENT || ' NEXT ' || NEXT_EXTENT  || ' );'
  FROM user_indexes
 WHERE table_type = 'TABLE'
   AND SUBSTR(index_name,1,3) <> 'SYS'
 ORDER BY index_name, seq;

/* ---------------------------------------------------------------------------- */

SELECT '
rem /*
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  * CREATE Sequences, start_with=min_value
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  */'
 FROM DUAL;

COLUMN sequence_name NOPRINT;
COLUMN seq           NOPRINT;
BREAK ON sequence_name SKIP 1;


SELECT sequence_name, 1 seq, 'CREATE SEQUENCE ' || LOWER ( sequence_name )
  FROM user_sequences
 UNION
SELECT sequence_name, 2 seq, '       INCREMENT BY ' || increment_by
  FROM user_sequences
 UNION
SELECT sequence_name, 3 seq, '         START WITH ' || min_value
  FROM user_sequences
 UNION
SELECT sequence_name, 4 seq, '           MAXVALUE ' || max_value
  FROM user_sequences
 UNION
SELECT sequence_name, 5 seq, '           MINVALUE ' || min_value
  FROM user_sequences
 UNION
SELECT sequence_name, 6 seq, DECODE(cycle_flag,'Y','              CYCLE ' ,NULL)
  FROM user_sequences
 UNION
SELECT sequence_name, 7 seq, '              CACHE ' || cache_size
  FROM user_sequences
 UNION
SELECT sequence_name, 8 seq, DECODE(order_flag,'Y','              ORDER;' ,';')
  FROM user_sequences
 ORDER BY sequence_name, seq;

/* ---------------------------------------------------------------------------- */

SELECT '
rem /*
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  * CREATE Triggers
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  */'
 FROM DUAL;

COLUMN trigger_name NOPRINT;
COLUMN seq          NOPRINT;
DEFINE max_length=20000
SET LONG &max_length

SELECT trigger_name, 1 seq, 'CREATE OR REPLACE TRIGGER ', description, trigger_body
  FROM user_triggers
 ORDER BY trigger_name, seq;

/* ---------------------------------------------------------------------------- */

SELECT '
rem /*
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  * CREATE Views
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  */'
 FROM DUAL;

DEFINE max_long_length=20000;

/* SELECT max(text_length) 
  INTO max_long_length
  FROM user_views; */

SET LONG &max_long_length

CLEAR COLUMN;
COLUMN view_name NOPRINT;
COLUMN seq       NOPRINT;
COLUMN text      FORMAT A150 WORD_WRAPPED;
SET COLSEP " "

SELECT view_name, 1 seq, 'CREATE OR REPLACE VIEW ' || lower ( view_name ) || ' AS ', text , ';'
  FROM user_views
 ORDER BY view_name, seq;


/* ---------------------------------------------------------------------------- */

SELECT '
rem /*
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  * CREATE Packages and Package Body
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  */'
 FROM DUAL;

SET LINESIZE 200
CLEAR COLUMN;
COLUMN name       NOPRINT;
COLUMN type       NOPRINT;
COLUMN line       NOPRINT;

BREAK ON name SKIP 1 ON type SKIP 1;

SELECT name, type, line, DECODE(SUBSTR(text,1,7), 'PACKAGE',null,
                                                  'AS     ',null,
                                                  '     ')  ||
                         text
  FROM user_source
 WHERE type <> 'PROCEDURE'
 ORDER by name, type, line;

SELECT '
rem /*
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  * CREATE Independent Procedures
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  */'
 FROM DUAL;

SELECT name, type, line, DECODE(SUBSTR(text,1,7), 'PACKAGE',null,
                                                  'AS     ',null,
                                                  'PROCEDU',null,
                                                  '     ')  ||
                         text
  FROM user_source
 WHERE type =  'PROCEDURE'
 ORDER by name, type, line;


/* ---------------------------------------------------------------------------- */

SELECT '
rem /*
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  * GRANT Table privaleges
rem  * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem  */'
 FROM DUAL;

SELECT 'GRANT ' || lower ( privilege ) || ' ON ' || lower ( table_name ) || ' TO ' ||
       lower ( grantee ) || DECODE ( grantable, 'YES',' WITH GRANT OPTION', null) ||
       ';'
  FROM user_tab_privs_made
 ORDER BY grantee, table_name, privilege;

spool off


exit;