ant-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From RADEMAKERS Tanguy <>
Subject Re: Creating database scripts via ant
Date Wed, 10 Mar 2004 18:59:36 GMT
Hello John,

Check out torque generator - it has ant tasks for reverse engineering a 
database as you discuss. Warning: learning curve ahead.


John Lindwall wrote:

>Thanks for the reply but your suggestion doesn't solve my problem (unless I'm doing something
>I'm using TOAD  I enabled the option "Tools/Spool SQL/To File..." and then executed
"Database/Export/Export Table Scripts".  In the SQL spool file I can indeed see the SQL being
issued by TOAD to perform this function (which is a pretty cool trick to remember for the
future).  But this captured SQL alone does not give me what I want.  
>The SQL that TOAD is issuing is allowing it to collect the info it needs to then generate
"CREATE TABLE" DDL and "CREATE CONSTRAINT" DDL, et al.  This is the raw material for the generation
of the DDL creation.  This DDL generation is the functionality I want to locate -- "point
the tool at a database and out comes a file filled with DDL to create that database".  TOAD
contains that code internally AFAIK so your suggestion doesn't solve my problem.
>Surely there's some open source tool that performs this task.
>Thanks anyway!
>FYI: Here's an example of the spooled SQL:
>3/9/2004 4:18:53 PM
>SELECT /*+ CHOOSE */ table_name, tablespace_name, pct_free, 
> pct_used, 
> initial_extent,
> next_extent,
> pct_increase,
> degree, instances, decode(ltrim(cache),'Y', 'CACHE', 'NOCACHE') cache
>, table_type, table_type_owner 
> FROM user_all_tables where 
> table_name=:tabname
>3/9/2004 4:18:53 PM
>Select /*+ CHOOSE */ column_name as Name, nullable,
> data_type as Type,
> Decode( data_type, 'NUMBER', data_precision + data_scale, data_length ) Length,
> data_precision Precision, data_scale Scale, data_length dlength, 
> data_default from
> user_tab_columns
> where table_name = :TABNAME
> order by column_id
>3/9/2004 4:18:53 PM
> select /*+ CHOOSE */  a1.constraint_name, 
> c1.column_name, c1.position
> from 
> user_cons_columns C1,  user_constraints A1
> where C1.table_name = A1.Table_name
> And  C1.constraint_name = A1.Constraint_Name
> And  C1.owner = A1.owner 
> and A1.constraint_type = 'P'
> and A1.table_name =:TabName
>-- etc etc
>-----Original Message-----
>From: Matt Reason []
>Sent: Tuesday, March 09, 2004 3:25 PM
>To: Ant Users List
>Subject: RE: Creating database scripts via ant
>Toad has a setting (I forget where - and it takes a day to find) that
>will show you all of the queries it is running to do it's dirty work.
>Once you enable it, you will see exactly what query it runs on the data
>dictionary to get the definition of your database.  Grab that query and
>save it to a file along with your custom statements.   Then use ant's
>sql task or exec to kick it off.
>-----Original Message-----
>From: John Lindwall [] 
>Sent: Tuesday, March 09, 2004 3:23 PM
>To: ''
>Subject: Creating database scripts via ant
>Our company maintains it's database definition via a  "master database"
>which is copied for production and test uses.  I prefer  to have SQL
>(DDL) scripts that create and populate my database for development  and
>testing.  What I do now is use a GUI tool called TOAD to create SQL
>files (from the master database) that create the database structure
>(table  structure, functions, sequences, etc).
>Whenever the master  database changes I need to manually run the GUI
>tool to regenerate the  scripts.  Then I customize them a little to
>clean up some 
>items.  I'd  rather run an ant target that generates these files for me
>so I can automate the  task.
>Any  pointers?
>To unsubscribe, e-mail:
>For additional commands, e-mail:

To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message