2

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() ?

Community
  • 1
  • 1
Neelam Sharma
  • 2,745
  • 4
  • 32
  • 73

1 Answers1

1

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.

Mike Gardner
  • 6,611
  • 5
  • 24
  • 34
  • @NeelamSharma you would need to call a command line shell, and execute commands from the shell. There are a number of examples here on StackOverflow, using `ProcessBuilder` function. – Mike Gardner Dec 30 '15 at 13:52
  • thanks, ddlegen helpd us! Can you suggest me about getting ddlgen using any schema (to get tables that are shared by other users) like we do in oracle with : select object_type from dba_objects where owner = ''user1' and object_name = 'user1' – Neelam Sharma Jan 06 '16 at 05:17
  • we want to get ddl using schema name, means we will user name and password of owner who shared the database objects to schema user like we have done in oracle using above command. We will pass user name and password of owner in ddlgen to get ddl for schema :ddlgen -Uowneruser -PownerPass -Sservername -Tobject_type -Nobject_name -Ddbname ...etc How can we achieve this? – Neelam Sharma Jan 08 '16 at 11:07
  • @NeelamSharma This comment thread should continue at the question: http://stackoverflow.com/questions/34628343/how-to-get-ddl-for-database-objects-shared-to-other-user – Mike Gardner Jan 08 '16 at 16:02