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.

asked 19 Sep, 22:04

gil's gravatar image

gil
263
accept rate: 0%

I am using SQL Anywhere 16.

(19 Sep, 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, 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, 11:42) Breck Carter

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, 23:19

John%20Smirnios's gravatar image

John Smirnios
9.4k378119
accept rate: 38%

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, 03:22

Volker%20Barth's gravatar image

Volker Barth
32.3k328474689
accept rate: 32%

edited 20 Sep, 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, 22:53) gil
Replies hidden

Feel free to accept that answer, then:)

(21 Sep, 04:22) Volker Barth
1

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, 07:45) Chris Keating
Your answer
toggle preview

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

Question tags:

×10

question asked: 19 Sep, 22:04

question was seen: 70 times

last updated: 21 Sep, 07:45