Re: creating sql scripts for existing tables ?

From: Ruud de Gunst <ruud_at_waubel.wau.mis.ah.nl>
Date: 1996/02/20
Message-ID: <Dn306q.KGI_at_wau.mis.ah.nl>#1/1

creinke_at_ccnet.com (Charles Reinke) wrote:

>The only way I've been able to create scripts for tables is to save them 
>at the time I type them in. Is there a way to create ".sql" scripts from 
>existing database in oracle ?  
 

>Thanks in advance, Chuck

Charles,

It's all in the datadictionary. Here's an example script for creating "create table" statements using the information stored in the Oracle datadictionary. This script does not produce the storage clause, but that's a feature you can add yourself.

Bye,
Ruud

Script:
rem

---

rem Program : get_tab.sql
rem Type    : SQL script
rem Input   : tab_owner    char    - owner of the table(s)
rem Output  : tmp.sql      file    - file, can be removed afterwards
rem           owner.tbr    file    - file containing create table

stats

rem Date    : 10-10-94
rem Author  : Ruud de Gunst, Bergler Nederland bv
rem Purpose : Generates create table statements for a given user.

rem

---

set heading off
set verify off
set feedback off
set pagesize 0
set linesize 80
set newpage 0

prompt
prompt Recreate create table statements
accept tab_owner char prompt ' for owner: ' prompt

set termout off

column spool_ext noprint new_value ext

select substr('&&tab_owner', 2, 3) spool_ext from sys.dual
/

spool tmp.sql

prompt spool &&tab_owner..tbr

select 'prompt prompt' || chr(10) ||

'prompt prompt Create table ' || tb.owner || '.' ||
tb.table_name || chr(10) ||

'prompt prompt' || chr(10) ||
'prompt CREATE TABLE ' || tb.owner || '.' || tb.table_name ||
chr(10) ||

'select rpad(decode' || chr(10) ||
' (tc.column_id' || chr(10) ||
' ,1, ''(''||tc.column_name' || chr(10) ||
' ,'',''||tc.column_name), 31, '' '') ||' ||
chr(10) ||

' rpad(decode' || chr(10) ||
' (tc.data_type' || chr(10) ||
' ,''DATE'', ''DATE'' ' || chr(10) ||
' ,''NUMBER'' ' || chr(10) ||
' ,''NUMBER(''||to_char(nvl(tc.data_precision,

tc.data_length))||' || chr(10) ||


       '               decode' || chr(10) ||

       '                 ('',''||to_char(tc.data_scale)' || chr(10) ||

       '                 ,'',0'', null' || chr(10) ||

       '                 ,'','',  null' || chr(10) ||

       '                 ,'',''||to_char(tc.data_scale)' || chr(10) ||

       '                 ) ||'')'' ' || chr(10) ||


       '

,tc.data_type||''(''||to_char(tc.data_length)||'')'' ' || chr(10) ||


       '             ), 16, '' '') ||' || chr(10) ||

       '       decode(tc.nullable' || chr(10) ||

       '             ,''N'' ' || chr(10) ||

       '             ,''NOT NULL'' ' || chr(10) ||

       '             ,''NULL'' ' || chr(10) ||

       '             )' || chr(10) ||

       'from   sys.dba_tab_columns tc' || chr(10) ||

       'where  tc.table_name = '||''''||table_name||'''' || chr(10) ||

       'and    tc.owner      = '||''''||owner||'''' || chr(10) ||

       'order by tc.column_id' || chr(10) ||

       '/' || chr(10) ||

       'prompt )' || chr(10) ||

       'prompt TABLESPACE '|| tablespace_name || chr(10) ||

       'prompt /' || chr(10) ||


       'prompt' || chr(10)


from   sys.dba_tables tb

where tb.owner like upper('&&tab_owner') order by tb.table_name
/

prompt spool off

spool off

_at_tmp.sql

clear columns
clear breaks

set heading on
set verify on
set feedback on
set pagesize 24
set newpage 1
set termout on Received on Tue Feb 20 1996 - 00:00:00 CET