How to generate the DDL SQL script from tables, triggers and constraints

Hi, I would like to know if it is possible to generate dll scripts of tables, triggers and constraints through select statement. if so, which commands return such scripts?

appreciate.

ddl

asked 19 Sep '18, 22:04

gil
41●3●3●6
accept rate: 0%

I am using SQL Anywhere 16.

(19 Sep '18, 22:06) gil

Are you trying to reverse-engineer existing DDL definitions, or create new DDL statements based on existing objects?

The former is best handled by dbunload, but the latter is not (e.g., generating complex shadow tables and their triggers, or funky MobiLink scripts).

(20 Sep '18, 11:39) Breck Carter

Comment Text Removed

> which commands return such scripts

A deep understanding of the STRING() function and LIST() aggregate function is one prerequisite for writing SQL to generate complex SQL. Another prerequisite is an understanding of the system catalog views.

(20 Sep '18, 11:42) Breck Carter

flat view

2 Answers:

active answers oldest answers newest answers popular answers

1	Your best best is dbunload -n. Generating the DDL statements for schema objects such as tables by querying the system tables is a large & complex undertaking. dbunload does it for you. permanent link answered 19 Sep '18, 23:19 John Smirnios 12.0k●3●96●166 accept rate: 37% flat view

Just to add to John's answer:

You can use "dbunload -n -t table, ..." to include only the listed tables in the reload.sql script (or use "... -e table,..." to exclude those) in case you just want scripts for particular tables.
For single tables, you can also use the sa_get_table_definition system procedure, its result contain all statements needed for the creation of a single table with indexes, FKs, triggers and privileges. - Of course you can call that procedure in a loop to generate many or all table definitions. - It does not include procedures, however.
Here's a similar question with lots of more details:
How Do I Extract Table DDL from SQL Anywhere

permanent link

answered 20 Sep '18, 03:22

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 20 Sep '18, 03:23

I need to capture the DDL from just one table at a time, so the function mentioned by @Volker Barth (sa_get_table_definition ()) is exactly what I was looking for. Thank you all.

(20 Sep '18, 22:53) gil

Replies hidden

Feel free to accept that answer, then:)

(21 Sep '18, 04:22) Volker Barth

Sybase Central can be used for this also. If you copy a selected object (i.e. a table) that will generate the DDL for that object which can be pasted into a text editor. This works for the broader schema if you need DDL for objects other than tables.

(21 Sep '18, 07:45) Chris Keating

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

ddl ×10

question asked: 19 Sep '18, 22:04

question was seen: 3,479 times

last updated: 21 Sep '18, 07:45

SAP SQL Anywhere

How to generate the DDL SQL script from tables, triggers and constraints

Follow this question

Related questions