Title: Schema Builder, Reverse Engineering
Author: Razi Chaudhry a director for RAZSoft Canada Inc. (www.razsoft.com), in Ottawa, Ontario, Canada. This script generates a SQL file from the Oracle DB and provides an excellent way to document a schema. The sql file will include all entries, for create table, indexes, primary keys, foreign keys, synonyms, views, packages and procedures. Excellent tool for the developers. Run it on your "SYS/SYSTEM" schema and see Oracle Internals.Source/Text/Comments
rem /*
rem * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem * Program : sch.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 * 03/23/99 : Razi Chaudhry : Program Created
rem * razic@sprint.ca
rem *
rem * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem * Authors: 1. Razsoft Canada, Inc. : http://www.razsoft.com (Razi)
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') || ' : Razi Chaudhry : Program Auto Generated from DB
rem *
rem * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
rem * Authors: 1. Razsoft Canada, Inc. : http://www.razsoft.com (Razi)
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;