| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Create Table SQL generator
This is a multi-part message in MIME format.
--------------1C625B05A3C Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit
Rob Mocking wrote:
> Given any table I am looking for a script/procedure that will generate > from the data-dictionary the corresponding CREATE TABLE (including > STORAGE clause) plus the necesary GRANTS and CONSTRAINT- and INDEX > definitions (sort of IMP with SHOW=Y, but then more flexible). > Can anybody help me out ?
I have attached a SQL*Plus file called d_tabddl.sql, which does just that (well, it doesn't do grants, but that can be easily added).
Chris
AVANCO International
--------------1C625B05A3C Content-Type: text/plain; charset=us-ascii; name="d_tabddl.sql" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="d_tabddl.sql" rem **************************************************************************rem Name: d_tabddl.sql (DBA Table Data Definition Language) rem Author: Chris Hamilton, TYC Associates, Inc. rem Date: 10-Jan-93, 20-Mar-93, May 1994.
rem Revised significantly, 24-Aug-94 - added OTHER indexes, enhanced rem indentation and appearance of output. rem May 1994 - Massive modifications. Added new ORACLE7 rem features such as defaults, check constraints, foreign key rem constraints, primary/unique keys constraints, other table indexes. rem August 1996, incorporated PL/SQL fix for multi-column foreign rem key constraint cartesion join problem, from Eurico Borges of rem Sonae Industria in Portugal. rem April/May 1997 - lots of revisions to format, etc. rem 09-Jun-1997 - added code to extract comments. rem Purpose: Table Script Extractor. rem This script extracts a table definition and all of its supporting rem objects definitions from the data dictionary and spools it to an rem OS file for archiving or modification.rem Usage: sqlplus -s un/pw @d_tabddl.sql
rem **************************************************************************
set space 0;
set verify off;
set numwidth 4;
set heading off;
set linesize 80;
set pagesize 0;
set feedback off;
set recsep off;
set trimspool on;
set long 255;
prompt ------------------------------------------------------;prompt DBA Table Creation Script Generator;
prompt ------------------------------------------------------; accept tabowner char prompt 'Table Owner: '; accept tabname char prompt 'Table Name: '; accept incltab char prompt 'Include TABLE definition? (Y/N): ' accept incldef char prompt 'Include DEFAULT definitions? (Y/N): ' accept inclchk char prompt 'Include CHECK constraints? (Y/N): ' accept inclfkey char prompt 'Include Foreign Key constraints? (Y/N): ' accept inclpkey char prompt 'Include Primary/Unique Key constraints? (Y/N): 'accept otherind char prompt 'Include Other Defined Indexes? (Y/N): ' accept inclcomm char prompt 'Include Table/Column Comment? (Y/N): ' rem accept filename char prompt 'Spool to <filename>: ';
prompt ------------------------------------------------------;
define filename='&&tabname..tab'
prompt Output will be spooled to &&filename.;
prompt ------------------------------------------------------;prompt Working...;
column remarks format a80;
column col0 format 999999990 noprint;
column col1 format a78;
column indent format a3;
column colname format a30;
column coltype format a15;
column colnull format a9;
spool &&filename;
rem --------------------------------------------------------------------------rem This query generates a file header.
rem --------------------------------------------------------------------------
select rpad('rem '||'&&filename',80,' ')||
rpad('rem '||'Generated on '||sysdate||' by '||
user||'.',80,' ')||
rpad('rem '||'Script to create the '||lower(nvl('&&tabowner',user))||
'.'||upper('&&tabname')||' table.',80,' ')||
rpad(' ',80,' ') remarks
from dual;
rem --------------------------------------------------------------------------
rem This query generates the CREATE TABLE line.
rem --------------------------------------------------------------------------
select 'create table '||table_name col1
from dba_tables
where owner = upper(nvl('&&tabowner',user))
and table_name = upper('&&tabname')
and upper(nvl('&&incltab','Y')) = 'Y';
rem --------------------------------------------------------------------------
rem This query generates column definitions for each column except the last.
rem --------------------------------------------------------------------------
select column_id col0,
decode(column_id, 1, ' (', ' ') indent,
lower(column_name)||' ' colname,
lower(data_type)||
decode(data_type,
'CHAR', '('||data_length||')',
'VARCHAR', '('||data_length||')',
'VARCHAR2', '('||data_length||')',
'DATE', null,
'LONG', null,
'RAW', null,
'LONG RAW', null)||
decode(data_type, 'NUMBER',
decode(data_precision, null,null,
'('||data_precision||','||data_scale||')'), null)||' ' coltype,
decode(nullable, 'N', 'not null', rpad(' ',8,' '))||',' colnull
from dba_tab_columns
where owner = upper(nvl('&&tabowner',user))
and table_name = upper('&&tabname')
and upper(nvl('&&incltab','Y')) = 'Y'
and column_id <
(select max(column_id)
from dba_tab_columns
where owner = upper(nvl('&&tabowner',user))
and table_name = upper('&&tabname'))
order by column_id;
rem --------------------------------------------------------------------------rem This query generates a column definition for the last column in the table.
rem --------------------------------------------------------------------------
select decode(column_id, 1, ' (', ' ') indent,
lower(column_name)||' ' colname,
lower(data_type)||
decode(data_type,
'CHAR', '('||data_length||')',
'VARCHAR', '('||data_length||')',
'VARCHAR2', '('||data_length||')',
'DATE', null,
'LONG', null,
'RAW', null,
'LONG RAW', null)||
decode(data_type, 'NUMBER',
decode(data_precision, null,null,
'('||data_precision||','||data_scale||')'), null)||' ' coltype,
decode(nullable, 'N', 'not null', rpad(' ',8,' '))||')' colnull
from dba_tab_columns
where owner = upper(nvl('&&tabowner',user))
and table_name = upper('&&tabname')
and upper(nvl('&&incltab','Y')) = 'Y'
and column_id =
(select max(column_id)
from dba_tab_columns
where owner = upper(nvl('&&tabowner',user))
and table_name = upper('&&tabname'));
column col1 format a80;
rem --------------------------------------------------------------------------rem This query generates the TABLESPACE and PCTFREE/PCTUSED definitions.
rem --------------------------------------------------------------------------
select ' '||
rpad(('tablespace '||lower(tablespace_name)),77,' ')||
' '||
rpad(('pctfree '||pct_free),77,' ')||
' '||
rpad(('pctused '||pct_used),77,' ')||
' '||
rpad(('initrans '||ini_trans),77,' ')||
' '||
rpad(('maxtrans '||max_trans),77,' ') col1
from dba_tables
where owner = upper(nvl('&&tabowner',user))
and table_name = upper('&&tabname')
and upper(nvl('&&incltab','Y')) = 'Y';
rem --------------------------------------------------------------------------
rem This query generates the STORAGE clause.
rem -------------------------------------------------------------------------- column col1 format a80; column col2 format a80; column col3 format a80; column col4 format a80; column col5 format a80;
select ' storage ('||
'initial '||round((initial_extent/1024),0)||'k ' col1,
' next '||round((next_extent/1024),0)||'k ' col2,
' pctincrease '||pct_increase col3,
' minextents '||min_extents col4,
' maxextents '||max_extents col5,
' freelists '||freelists col6,
' freelist groups '||freelist_groups||');' col7
from dba_tables
where owner = upper(nvl('&&tabowner',user))
and table_name = upper('&&tabname')
and upper(nvl('&&incltab','Y')) = 'Y';
rem --------------------------------------------------------------------------
rem This query separates the sections and labels the DEFAULT section.
rem --------------------------------------------------------------------------
select 0 col0,
null col1
from dual
'rem Column DEFAULT Definitions;' col1
from dual
where upper(nvl('&&incldef','Y')) = 'Y'
order by 1;
rem --------------------------------------------------------------------------rem This query generates ALTER TABLE statements to define column defaults.
rem --------------------------------------------------------------------------
column col1 format a80;
column col2 format a80;
column col3 format a2;
column col4 format a77;
column col5 format a4;
rem HEY DO SOME PL/SQL HERE TO FIX THE LINE WRAP PROBLEM.
select column_id col0,
'alter table '||
lower(table_name) col1,
' modify ('||lower(column_name)||' default ' col2,
' ' col3,
data_default col4,
' );' col5
from dba_tab_columns
where owner = upper(nvl('&&tabowner',user))
and table_name = upper('&&tabname')
and default_length is not null
and upper(nvl('&&incldef','Y')) = 'Y';
rem --------------------------------------------------------------------------
rem This query separates the sections and labels the CHECK section.
rem --------------------------------------------------------------------------
select 0 col0,
null col1
from dual
'rem CHECK Constraint Definitions;' col1
from dual
where upper(nvl('&&inclchk','Y')) = 'Y'
order by 1;
rem --------------------------------------------------------------------------rem This query generates ALTER TABLE statements to define CHECK constraints.
rem --------------------------------------------------------------------------
column col1 format a80;
column col2 format a80;
column col3 format a9;
column col4 format a69;
column col5 format a4;
select 'alter table '||
lower(dc.table_name) col1,
' add constraint '||
lower(dc.constraint_name) col2,
' check (' col3,
dc.search_condition col4,
' );' col4
from dba_constraints dc
where dc.owner = upper(nvl('&&tabowner',user))
and dc.table_name = upper('&&tabname')
and dc.constraint_type = 'C'
and dc.constraint_name not like 'SYS_C%'
and upper(nvl('&&inclchk','Y')) = 'Y'
order by dc.constraint_name;
rem --------------------------------------------------------------------------rem This query separates the sections and labels the FOREIGN KEY Constraints.
rem -------------------------------------------------------------------------- column col1 format a80;
select 0 col0,
null col1
from dual
'rem Foreign Key Constraint Definitions;' col1
from dual
where upper(nvl('&&inclfkey','Y')) = 'Y'
order by 1;
rem --------------------------------------------------------------------------;rem This PL/SQL block generates the FOREIGN KEY constraint definitions.;
rem --------------------------------------------------------------------------;
set serveroutput on;
begin
declare
fk_cols varchar2(140);
r_columns varchar2(140);
r_table varchar2(30);
lixo varchar2(30);
aux_col varchar2(30);
ptabowner varchar2(30);
ptabname varchar2(30);
cursor col
(wconstraint_name dba_cons_columns.constraint_name%type,
wowner dba_cons_columns.owner%type) is
select column_name,
table_name
from dba_cons_columns
where constraint_name = upper(wconstraint_name)
and owner = upper(wowner)
order by position;
begin
ptabowner := upper(nvl('&&tabowner',user));
ptabname := upper(nvl('&&tabname',user));
blank1 := ' ';
blank2 := ' ';
if (upper(nvl('&&inclfkey','Y')) = 'Y') then
/* search all 'R' constraints */
for reg_cons in constra
loop
fk_cols :='';
r_columns :='';
aux_col :='';
/* to know our table constraint columns */
open col(reg_cons.constraint_name,reg_cons.owner);
loop
fetch col into aux_col,lixo;
if col%found then
fk_cols :=fk_cols||aux_col||',';
else
exit;
end if;
end loop;
close col;
fk_cols :=
substr(fk_cols,1,(length(fk_cols)-1));
aux_col:='';
/* to know referenced table constraint columns */
open col(reg_cons.r_constraint_name, reg_cons.r_owner);
loop
fetch col into aux_col,r_table;
if col%found then
r_columns := r_columns||aux_col||',';
else
exit;
end if;
end loop;
close col;
r_columns :=
substr(r_columns,1,(length(r_columns)-1));
aux_col:='';
/* build the script line */
dbms_output.put_line('alter table '||
lower(reg_cons.table_name));
dbms_output.put_line(blank2||'add constraint'||
blank1||lower(reg_cons.constraint_name));
dbms_output.put_line(blank2||'foreign key ('||
lower(fk_cols)||')');
dbms_output.put_line(blank2||'references'||
blank1||lower(r_table)||blank1||
'('||lower(r_columns)||');');
end loop;
end;
end;
/
rem --------------------------------------------------------------------------rem This query separates the sections and labels the PRIMARY KEY and rem UNIQUE constraints.
rem --------------------------------------------------------------------------
select 0 col0,
null col1
from dual
'rem Primary Key and Unique Constraint Definitions;' col1
from dual
where upper(nvl('&&inclpkey','Y')) = 'Y'
order by 1;
rem --------------------------------------------------------------------------rem This query generates the PRIMARY KEY and UNIQUE constraint definitions, rem along with their associated index storage parameters.
rem --------------------------------------------------------------------------
select do.object_id col0,
rpad(('alter table '||lower(dc.table_name)),80,' ')||
rpad((' add constraint '||lower(dc.constraint_name)),80,' ') col1
from dba_objects do,
dba_constraints dc
where do.owner = dc.owner
and do.object_name = dc.constraint_name
and dc.owner = upper(nvl('&&tabowner',user))
and dc.table_name = upper('&&tabname')
and dc.constraint_type in ('P', 'U')
and upper(nvl('&&inclpkey','Y')) = 'Y'
union all
' '||decode(dcc1.position,
1,decode(dc1.constraint_type,'P','primary key','U','unique')||' (',
decode(dc1.constraint_type,'P',' ','U',' '))||
lower(dcc1.column_name)||',' col1
from dba_cons_columns dcc1,
dba_constraints dc1,
dba_objects do1
where do1.owner = dc1.owner
and do1.object_name = dc1.constraint_name
and dc1.owner = dcc1.owner
and dc1.constraint_name = dcc1.constraint_name
and dcc1.owner = upper(nvl('&&tabowner',user))
and dcc1.table_name = upper('&&tabname')
and dc1.constraint_type in ('P', 'U')
and upper(nvl('&&inclpkey','Y')) = 'Y'
and dcc1.position <
(select max(dcc2.position)
from dba_cons_columns dcc2
where dcc2.owner = dc1.owner
and dcc2.constraint_name = dc1.constraint_name)
union all
' '||decode(dcc1.position,
1,decode(dc1.constraint_type,'P','primary key','U','unique')||' (',
decode(dc1.constraint_type,'P',' ','U',' '))||
lower(dcc1.column_name)||')' col1
from dba_cons_columns dcc1,
dba_constraints dc1,
dba_objects do1
where do1.owner = dc1.owner
and do1.object_name = dc1.constraint_name
and dc1.owner = dcc1.owner
and dc1.constraint_name = dcc1.constraint_name
and dcc1.owner = upper(nvl('&&tabowner',user))
and dcc1.table_name = upper('&&tabname')
and dc1.constraint_type in ('P', 'U')
and upper(nvl('&&inclpkey','Y')) = 'Y'
and dcc1.position =
(select max(dcc2.position)
from dba_cons_columns dcc2
where dcc2.owner = dcc1.owner
and dcc2.constraint_name = dcc1.constraint_name)
union all
rpad(' using index',80,' ')||
rpad((' tablespace '||lower(di.tablespace_name)),80,' ')||
rpad((' pctfree '||di.pct_free),80,' ') col1
from dba_objects do,
dba_indexes di,
dba_constraints dc
where do.owner = di.owner
and do.object_name = di.index_name
and di.owner = dc.owner
and di.index_name = dc.constraint_name
and dc.constraint_type in ('P', 'U')
and di.table_name = upper('&&tabname')
and di.table_owner = upper(nvl('&&tabowner',user))
and upper(nvl('&&inclpkey','Y')) = 'Y'
union all
rpad((' storage ('||'initial '||round((initial_extent/1024),0)||
'k '||'next '||round((next_extent/1024),0)||'k '||
'maxextents '||max_extents||' '||
'pctincrease '||pct_increase||');'),80,' ') col1
from dba_objects do,
dba_indexes di,
dba_constraints dc
where do.owner = di.owner
and do.object_name = di.index_name
and di.owner = dc.owner
and di.index_name = dc.constraint_name
and dc.constraint_type in ('P', 'U')
and di.table_name = upper('&&tabname')
and di.table_owner = upper(nvl('&&tabowner',user))
and upper(nvl('&&inclpkey','Y')) = 'Y'
order by 1;
rem -------------------------------------------------------------------------;rem This query separates the sections and labels the other INDEXES rem (those that are NOT defined as PRIMARY KEY or UNIQUE constraints).
rem -------------------------------------------------------------------------;
select 0 col0,
null col1
from dual
'rem Other Index Definitions;' col1
from dual
where upper(nvl('&&otherind','Y')) = 'Y'
order by 1;
rem --------------------------------------------------------------------------;rem This query generates the CREATE INDEX statement for indexes that rem are NOT defined in UNIQUE or FOREIGN KEY constraints.
rem --------------------------------------------------------------------------;
select do.object_id col0,
rpad(('create '||decode(di.uniqueness,'UNIQUE','unique ',null)||
'index '||lower(di.index_name)),80,' ')||
' on '||lower(di.table_name) col1
from dba_objects do,
dba_indexes di
where do.owner = di.owner
and do.object_name = di.index_name
and di.owner = upper(nvl('&&tabowner',user))
and di.table_name = upper('&&tabname')
and upper(nvl('&&otherind','Y')) = 'Y'
and (di.owner, di.index_name) not in
(select dc.owner,
dc.constraint_name
from dba_constraints dc,
dba_tables dt
where dc.owner = dt.owner
and dc.table_name = dt.table_name
and dt.owner = upper(nvl('&&tabowner',user))
and dt.table_name = upper('&&tabname')
and dc.constraint_type in ('P', 'U'))
union all
' '||decode(dic1.column_position, 1,'(', ' ')||
lower(dic1.column_name)||',' col1
from dba_objects do1,
dba_ind_columns dic1,
dba_indexes di1
where do1.owner = di1.owner
and do1.object_name = di1.index_name
and di1.owner = dic1.index_owner
and di1.index_name = dic1.index_name
and di1.table_owner = upper(nvl('&&tabowner',user))
and di1.table_name = upper('&&tabname')
and upper(nvl('&&otherind','Y')) = 'Y'
and dic1.column_position <
(select max(dic2.column_position)
from dba_ind_columns dic2
where dic2.index_owner = di1.owner
and dic2.index_name = di1.index_name)
and (di1.owner, di1.index_name) not in
(select dc.owner,
dc.constraint_name
from dba_constraints dc,
dba_tables dt
where dc.owner = dt.owner
and dc.table_name = dt.table_name
and dt.owner = upper(nvl('&&tabowner',user))
and dt.table_name = upper('&&tabname')
and dc.constraint_type in ('P', 'U'))
union all
' '||decode(dic1.column_position, 1,'(', ' ')||
lower(dic1.column_name)||')' col1
from dba_objects do1,
dba_ind_columns dic1,
dba_indexes di1
where do1.owner = di1.owner
and do1.object_name = di1.index_name
and di1.owner = dic1.index_owner
and di1.index_name = dic1.index_name
and di1.table_owner = upper(nvl('&&tabowner',user))
and di1.table_name = upper('&&tabname')
and upper(nvl('&&otherind','Y')) = 'Y'
and dic1.column_position =
(select max(dic2.column_position)
from dba_ind_columns dic2
where dic2.index_owner = di1.owner
and dic2.index_name = di1.index_name)
and (di1.owner, di1.index_name) not in
(select dc.owner,
dc.constraint_name
from dba_constraints dc,
dba_tables dt
where dc.owner = dt.owner
and dc.table_name = dt.table_name
and dt.owner = upper(nvl('&&tabowner',user))
and dt.table_name = upper('&&tabname')
and dc.constraint_type in ('P', 'U'))
union all
rpad((' tablespace '||lower(di.tablespace_name)),80,' ')||
rpad((' pctfree '||di.pct_free),80,' ') col1
from dba_objects do,
dba_indexes di
where do.owner = di.owner
and do.object_name = di.index_name
and di.table_name = upper('&&tabname')
and di.table_owner = upper(nvl('&&tabowner',user))
and upper(nvl('&&otherind','Y')) = 'Y'
and (di.owner, di.index_name) not in
(select dc.owner,
dc.constraint_name
from dba_constraints dc,
dba_tables dt
where dc.owner = dt.owner
and dc.table_name = dt.table_name
and dt.owner = upper(nvl('&&tabowner',user))
and dt.table_name = upper('&&tabname')
and dc.constraint_type in ('P', 'U'))
union all
' storage (initial '||round((initial_extent/1024),0)||'k '||
'next '||round((next_extent/1024),0)||'k '||
'maxextents '||max_extents||' '||
'pctincrease '||pct_increase||');' col1
from dba_objects do,
dba_indexes di
where do.owner = di.owner
and do.object_name = di.index_name
and di.table_name = upper('&&tabname')
and di.table_owner = upper(nvl('&&tabowner',user))
and upper(nvl('&&otherind','Y')) = 'Y'
and (di.owner, di.index_name) not in
(select dc.owner,
dc.constraint_name
from dba_constraints dc,
dba_tables dt
where dc.owner = dt.owner
and dc.table_name = dt.table_name
and dt.owner = upper(nvl('&&tabowner',user))
and dt.table_name = upper('&&tabname')
and dc.constraint_type in ('P', 'U'))
order by 1;
rem --------------------------------------------------------------------------rem This query separates the sections and labels the COMMENTS.
rem --------------------------------------------------------------------------
select 0 col0,
null col1
from dual
'rem Table and Column Documentation Comments' col1
from dual
where upper(nvl('&&inclcomm','Y')) = 'Y'
order by 1;
set arraysize 1;
set linesize 2000;
-- column col1 format a80;
column txt format a2000 word_wrap;
select '0' col0,
'comment on table ' ||
dtc.table_name ||
' is ' ||
chr(39) ||
dtc.comments ||
chr(39) ||
';' txt
from dba_tab_comments dtc
where owner = upper(nvl('&&tabowner',user))
and table_name = upper('&&tabname')
and upper(nvl('&&inclcomm','Y')) = 'Y'
union all
'comment on column ' ||
dcc.table_name ||
'.' ||
dcc.column_name ||
' is ' ||
chr(39) ||
dcc.comments ||
chr(39) ||
';' txt
from dba_col_comments dcc
where owner = upper(nvl('&&tabowner',user))
and table_name = upper('&&tabname')
and upper(nvl('&&inclcomm','Y')) = 'Y'
order by 1, 2;
spool off;
rem exit;
--------------1C625B05A3C-- Received on Mon Aug 04 1997 - 00:00:00 CDT
 |
 |