?
Solved

How to generate DDL ...

Posted on 2000-05-10
5
Medium Priority
?
1,463 Views
Last Modified: 2012-08-13
How to generate DDL (for tables only)from existing database without using export? Is there any script available?

Thanks.

0
Comment
Question by:iqbalj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 2

Expert Comment

by:NetoMan
ID: 2798280
I suggest you use a freeware tool called TOAD. there is a site where you can find a free version :

http://www.toadsoft.com/ 

You can get scripts of ypur tables, procedures, functions. Also many other capabilities.

Also you can use :

  DESC tablename

to see all the table definitions and build your own scipt-making procedure.

from SELECT * FROM USER_TABLES you can get all the tables you own.

NetoMan :)
0
 

Author Comment

by:iqbalj
ID: 2798346
Actually I'm looking for a script so I can reproduce the tables off site (little complicated). I'm supporting a dumb client who needs to generate the DDL and send it to us.

At this point I can't use either TOAD or export....

Thanks a lot.
0
 
LVL 4

Expert Comment

by:dda
ID: 2798779
I've found this script at the http://www.dbatoolz.com/ site (category 9: Table scripts). There are a lot of usefull stuff there.

rem    
rem     File:        c_tab_rec.sql
rem     Created:     Vitaliy Mogilevskiy (vit100gain@earthlink.net)
rem     Desc:        Creates DDL script to re-create a table
rem    

set term on
set feedback on
set verify off
set pages 999
set head off
set lines 132
col col_name format a35
col table_name noprint new_value m_table_name
col type format a15
col nn_or_n format a11

break on table_name skip page
ttitle  -
         m_table_name -

accept shema_owner char prompt "Enter Shema Owner Name [Enter For All]:"
accept tab_name char prompt "Enter Table Name [Enter For All]:"

select 'create table '||cl.owner||'.'||cl.table_name||'_new ('                          table_name
,       cl.column_name                                                                  col_name
,       decode(cl.data_type,
                 'CHAR','CHAR('||cl.data_length||')',
              'VARCHAR','VARCHAR('||cl.data_length||')',
             'VARCHAR2','VARCHAR2('||cl.data_length||')',
               'NUMBER',decode(cl.data_precision,
                                  NULL,'NUMBER'
                                      ,'NUMBER('||cl.data_precision||','||cl.data_scale||')')
                       ,cl.data_type)                                                           type
,       decode(cl.nullable,
                'Y','NULL',
                'N','NOT NULL')||decode(cl.column_id,
                                        gr.mx_col,');'
                                                 ,',' )                                 nn_or_n
from dba_tab_columns          cl
,    (select max(column_id) mx_col, table_name
      from   dba_tab_columns
      group by table_name)    gr
where cl.owner = nvl(upper('&shema_owner'),cl.owner)

and   cl.table_name = nvl(upper('&tab_name'),cl.table_name)

and   cl.table_name = gr.table_name
order by cl.owner||'.'||cl.table_name
,        cl.column_id
/
0
 

Author Comment

by:iqbalj
ID: 2806085
Thnaks a lot. dbatoolz is a good site. I was looking for a script to generate all tables DDL not just one. I'll modify the script.

You can collect the points if you want.
0
 
LVL 4

Accepted Solution

by:
dda earned 100 total points
ID: 2806442
Many thanks to Vitaliy Mogilevskiy (www.dbatoolz.com).
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question