Script to Extract Table Definition

From: Chris Hamilton <chamilto_at_tyc.com>
Date: 1996/03/04
Message-ID: <4her8l$3hu_at_news.cais.com>

The following script extracts a table definition from the data dictionary, including defaults, constraints, indexes, unique and primary keys, foreign keys, and storage parameters. Use and enjoy.

Chris

---

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 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 _at_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 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 filename char prompt 'Spool 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 '||'Auto-generated on '||sysdate||' by '||user||'.',80,' ')||
         rpad('rem '||'Script to create the '||'&&tabowner'||'.'||
           upper('&&tabname')||' table and associated objects.',80,' ')||
         rpad(' ',80,' ')  remarks
from     dual; 

rem --------------------------------------------------------------------------

rem This query generates the CREATE TABLE line.

rem --------------------------------------------------------------------------

select   'create table '||lower(owner)||'.'||table_name col1
from     dba_tables
where    owner = upper('&&tabowner')
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,
         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('&&tabowner')
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('&&tabowner')
          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,
         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('&&tabowner')
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('&&tabowner')
          and    table_name = upper('&&tabname'));

column col1 format a80;

rem --------------------------------------------------------------------------

rem This query generates the TABLESPACE and PCTFREE/PCTUSED definitions.

rem --------------------------------------------------------------------------

select   '   '||
         rpad(('tablespace '||tablespace_name),77,' ')||


         '   '||

         rpad(('pctfree '||pct_free||' '||'pctused '||pct_used),77,' ')
col1
from     dba_tables
where    owner = upper('&&tabowner')
and      table_name = upper('&&tabname')
and      upper(nvl('&&incltab','Y')) = 'Y';

rem --------------------------------------------------------------------------

rem This query generates the STORAGE clause.

rem --------------------------------------------------------------------------

select   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_tables
where    owner = upper('&&tabowner')
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

where upper(nvl('&&incldef','Y')) = 'Y' union all
select 1 col0,

'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 a2;
column col3 format a76;
column col4 format a2;

select column_id col0,

'alter table '||

         lower(owner)||'.'||
         table_name||


         ' modify ('||column_name||' default ' col1,


         '  ' col2,


         data_default col3,


         ');' col4


from     dba_tab_columns
where    owner = upper('&&tabowner')
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

where upper(nvl('&&inclchk','Y')) = 'Y' union all
select 1 col0,

'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 a9;
column col3 format a69;
column col4 format a2;

select rpad('alter table '||

         lower(owner)||'.'||
         dc.table_name||


         ' add constraint '||


         dc.constraint_name,78,' ') col1,


         '  check (' col2,


         dc.search_condition col3,


         ');' col4


from     dba_constraints dc
where    dc.owner = upper('&&tabowner')
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;

column col1 format a80;
column col2 format a80;
column col3 format a80;

rem --------------------------------------------------------------------------

rem This query separates the sections and labels the FOREIGN KEY Constraints.

rem --------------------------------------------------------------------------

select   0 col0,
         null col1
from     dual

where upper(nvl('&&inclfkey','Y')) = 'Y' union all
select 1 col0,

'rem Foreign Key Constraint Definitions;' col1
from dual
where upper(nvl('&&inclfkey','Y')) = 'Y'

         order by 1;

rem --------------------------------------------------------------------------

rem This query generates the FOREIGN KEY constraint definitions.

rem --------------------------------------------------------------------------

select   rpad('alter table '||
         lower(dc1.owner)||'.'||
         dc1.table_name||


         ' add constraint '||


         dc1.constraint_name,80,' ') col1,
         rpad('  foreign key ('||dcc1.column_name||') references '||
         lower(dcc2.owner)||'.'||dcc2.table_name||' ('||
         dcc2.column_name||');',80,' ') col2
from     dba_constraints dc1,
         dba_cons_columns dcc1,
         dba_constraints dc2,
         dba_cons_columns dcc2
where    dc1.owner = dcc1.owner
and      dc1.constraint_name = dcc1.constraint_name
and      dc1.r_owner = dc2.owner
and      dc1.r_constraint_name = dc2.constraint_name
and      dc2.owner = dcc2.owner
and      dc2.constraint_name = dcc2.constraint_name
and      dc1.owner = upper('&&tabowner')
and      dc1.table_name = upper('&&tabname')
and      dc1.constraint_type = 'R'
and      upper(nvl('&&inclfkey','Y')) = 'Y'

order by dc1.constraint_name;

rem --------------------------------------------------------------------------

rem This query separates the sections and labels the PRIMARY KEY and rem UNIQUE constraints.

rem --------------------------------------------------------------------------

select   0 col0,
         null col1
from     dual

where upper(nvl('&&inclpkey','Y')) = 'Y' union all
select 1 col0,

'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('&&tabowner')||'.'||dc.table_name||


         ' add constraint '||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('&&tabowner')
and      dc.table_name = upper('&&tabname')
and      dc.constraint_type in ('P', 'U')
and      upper(nvl('&&inclpkey','Y')) = 'Y'

union all
select (do1.object_id + (dcc1.position/10)) col0,

' '||decode(dcc1.position,

           1,decode(dc1.constraint_type,'P','primary key','U','unique')||' (',
           decode(dc1.constraint_type,'P','             ','U','        '))||
           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('&&tabowner')
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
select (do1.object_id + (dcc1.position/10)) col0,

' '||decode(dcc1.position,

           1,decode(dc1.constraint_type,'P','primary key','U','unique')||' (',
           decode(dc1.constraint_type,'P','             ','U','        '))||
           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('&&tabowner')
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
select (do.object_id + .998) col0,

         rpad('  using index',80,' ')||
         rpad(('  tablespace '||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('&&tabowner')
and      upper(nvl('&&inclpkey','Y')) = 'Y'

union all
select (do.object_id + .999) col0,

         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('&&tabowner')
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

where upper(nvl('&&otherind','Y')) = 'Y' union all
select 1 col0,

'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,


         'create '||decode(di.uniqueness,'UNIQUE','unique ',null)||


         'index '||lower(di.owner)||'.'||di.index_name||' on '||


         lower(di.table_owner)||'.'||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('&&tabowner')
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('&&tabowner')
          and    dt.table_name = upper('&&tabname')
          and    dc.constraint_type in ('P', 'U'))

union all
select (do1.object_id + (dic1.column_position/10)) col0,

' '||decode(dic1.column_position, 1,'(', ' ')||

         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('&&tabowner')
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('&&tabowner')
          and    dt.table_name = upper('&&tabname')
          and    dc.constraint_type in ('P', 'U'))

union all
select (do1.object_id + (dic1.column_position/10)) col0,

' '||decode(dic1.column_position, 1,'(', ' ')||

         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('&&tabowner')
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('&&tabowner')
          and    dt.table_name = upper('&&tabname')
          and    dc.constraint_type in ('P', 'U'))

union all
select (do.object_id + .998) col0,

         rpad(('  tablespace '||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('&&tabowner')
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('&&tabowner')
          and    dt.table_name = upper('&&tabname')
          and    dc.constraint_type in ('P', 'U'))

union all
select (do.object_id + .999) col0,

' 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('&&tabowner')
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('&&tabowner')
          and    dt.table_name = upper('&&tabname')
          and    dc.constraint_type in ('P', 'U'))

order by 1;

spool off;

pause Press <Return> to continue;

rem exit;

---

Received on Mon Mar 04 1996 - 00:00:00 CET