We are working with Sybase database. We are using Sybase ASE 15.5 version on Windows 7 machine and we have created one sample java program that connect with Sybase using jdbc : com.sybase.jdbc4.jdbc.SybDataSource. We are able to connect and run query on sybase database.
Our requirement is to generate DDL statements : create table scripts, indexes, stored procedure, triggers, synonyms, functions etc. We want to generate SQL script on all database objects.
We have tried to extract using sysobjects and syscolumns as per suggestion give here but that only produce table names user table and system tables from : SELECT * FROM sysobjects WHERE type = 'U'.
On researching over reverse engineer sybase using schemaspy tool that visualize the relationship between tables/views. But that of no use, we need script having ddl commands.
On exploring more found that ddlgen extracts all sorts of DDL statements (tables, indexes, etc.), how can I extract these statement in my java program using ddlgen?
OR can there be any other solution that will help us to extract ddl of a sybase database like in case of oracle we get using metaModel.getDDL() ?
I believe you can retrieve some of the ddl from syscomments and sysobjects.
From the documentations:
syscomments contains entries for each view, rule, default, trigger, table constraint, partition, procedure, computed column, function-based index key, and other forms of compiled objects. The text column contains the original definition statements. If the text column is longer than 255 bytes, the entries span rows. Each object can occupy as many as 65,025 rows
SELECT so.name, sc.text
FROM syscomments sc, sysobjects so
WHERE sc.id = so.id
AND sc.texttype = 1 --1 for user defined objects/0 for system objects
ORDER BY so.name
Obviously this isn't quite complete, but it should get you pretty close. Alternatively, you would need your java program to call the command line ddlgen utility.
I don't have an ASE install at the moment, so I apologize if the code isn't quite right.