sybase 导出DDL语句以及ddlgen的描述

工具路径：

%sybase%/ASEP/bin

注：%sybase%为客户端安装目录

语法：

ddlgen -Uusername -Ppassword -Sserver –Ddbname –Tobject_type –Nobject_name

-T数据库对象类型

-N对象名称 （必须同-T一起使用,缺省–N%）

例：

导出test库中所有对象的ddl语句

ddlgen -Uusername -Ppassword -Sserver –Dtest

导出test库中test用户下所有以t开头的用户表的ddl语句

ddlgen -Utest -Ppassword –Sapp77 -Dtest -TU -Nt%

ddlgen

Description

A Java-based tool that generates definitions forserver- and database-level objects in AdaptiveServer.ddlgensupports Adaptive Serverversion 11.9.2 and later.

The command-line version ofddlgenis located in$SYBASE/ASEP/bin(%SYBASE%\ASEP\bininWindows).

Syntax

ddlgen

-Ulogin

-Ppassword

-S[server|host_name:port_number]

[-Iinterfaces_file]

[-Tobject_type]

[-Nobject_name]

[-Ddbname]

[-Xextended_object_type]

[-Ooutput_file]

[-Eerror_file]

[-Lprogress_log_file]

[-Jclient_charset]

-F[ % | SGM | GRP | USR | R | D | UDD | U | V |

P | XP | I | RI | KC | TR | PC ]

Or

ddlgen -v

Parameters

-Ulogin

specifies a login name, and is case-sensitive.

-Ppassword

specifies your password.

-S [server|host_name:port_number]

specifies the name of the Adaptive Server.ddlgenlooks this name up in the interfaces fileorLDAPconfiguration file. If you specify:

·
-S [host_name:port_number] –
ddlgenuses thehost_nameandport_numberprovided, and neither interfaces norLDAPconfiguration files are read or parsed.

·
-S[server] -I–ddlgenparses the interfaces file specified at the user location for the servername (see the-Iparameter description for more information).

·
-S[server]– without specifying an interfaces file,ddlgendoes the following:

a.
ddlgenfirst tries to read theLDAPconfiguration file from the standard location

b.
If theLDAPfile does not exist, or exists but does not contain an Adaptive Serverentry, then the interfaces file is parsed at its standard location for theserver name

c.
If theLDAPfile exists, thenddlgenuses it to search the server name. The interfaces file is not parsed, andtheLDAPconfiguration file is parsed.

You must use the-Soption becauseddlgendoes not connect to a default server.

-I

specifies the interfaces file name, and corresponds to$SYBASE/interfacesforUNIX, and%SYBASE%\ini\sql.iniforWindows. Use thisoptional parameter with-S.

-Tobject_type

specifies the type of object you are creating. If you do not use-T,ddlgengenerates DDL for the default database of login. The object types for-Tare:

Object type	Description
C	cache
D	default
DB	database
DBD	database device
DPD	dump device
EC	execution class
EG	engine group
EK	encrypted keys
GRP	group
I	index
KC	key constraints
L	login
LK	logical key
P	stored procedure
R	rule
RI	referential integrity
RO	role
RS	remote server
SGM	segment
TR	trigger
U	table
UDD	user-defined datatype
USR	user
V	view
WS	user-defined Web service
WSC	Web service consumer
XP	extended stored procedure

-Nobject_name

specifies the fully qualified name of the object you are creating, such as-Ndb_name.owner_name.table_name.object_name. The-Noption:

·
is required if you specify anyobject_typeother thanDB(database) in the-Tparameter.

·
accepts wildcards with the use of%.

·
generates DDL for all items of a specific object type on your server.

·
enforces strict order in which it parses the names in the-Ndb_name.owner_name.table_name.object_nameformat. If you only provide three arguments,ddlgenassumes they areowner_name,table_name, andobject_name, in that order. Alternatively, you can alsouse-Nowner_name.table_name-Ddb_name.ddlgendoes not impose this restriction ifobject_nameis an index (I).

-Ddbname

specifies the name of the database for the object you specify in the-Noption. The default is the user’s defaultdatabase.

-Xextended_object_type

differentiates the following:

·
user tables (OU) from proxy tables (OD) when you specify atable as your object type (-TU)

·
temporary databases (OD) from nontemporary databases (OU)when you specify database as your object type (-TDB)

·
SQLJprocedures (OD) from stored procedures (OU) when you specifyprocedure as your object type (-TP).

Ifobject_type(-T) isU(table) and-Xis not specified,ddlgengenerates DDL for both user tables and proxy tables. To generate DDL onlyfor:

·
user tables– use theOUextended object type with the-Xoption.

·
proxy tables– use theODextended object type with the-Xoption.

ddlgendoes not support schema generation for system tables.

-Ooutput_file

specifies an output file for the generated DDL. If you do not specify-O, the DDL you create appears in a consolewindow.

-Eerror_file

specifies a log file for recording errors. If you do not specify-E, the generated errors appear in a consolewindow.

-Lprogress_log_file

specifies a log file for recording the progress ofddlgen. If you do not specify-L, the progress is not recorded.

-Jclient_charset

specifies the character set to use on the client.-Jclient_charsetrequests thatAdaptive Server convert to and fromclient_charset, the character set used on the client. Afilter converts input betweenclient_charsetand the Adaptive Server character set.

Omitting-Jsets the character set to a default for the platform. The default may notnecessarily be the character set that the client is using.

HP platforms – Youmustuse-Jiso_1to specify the correct character set.

-F

filters out indexes, triggers, and constraintsout of table and database definitions in the DDL of table- anddatabase-level objects. The valid filters are:

·
For tables–[ % | I | RI | KC | TR | PC]

·
For databases–[ % | SGM | GRP | USR | R | D |UDD | U | V | P | XP | I | RI | KC | TR]

The filter options are:

Filter option	Filters out:
%	Everything, and retrieves the schema-only definition of a database or table.
SGM	Segments
GRP	Groups
USR	Users
R	Rules
D	Defaults
UDD	Uer-defined datatypes
U	User tables
V	Views
P	Stored procedures
PC	Partition condition
XP	Extended stored procedures
I	Indexes
RI	Referential integrity constraints
KC	Primary- and unique-key constraints
TR	Triggers

-v

displays the version and copyright message ofddlgenand returns to the operating system.

Examples

Example 1

Caches– Generates DDL for a cache calleddefault data cacheon a machine namedHARBORusing port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TC-N"default data cache"

To generate DDL for all caches:

ddlgen -Ulogin-Ppassword-Sserver:port-TC-N%

Example 2

Defaults– Generates DDL for a default called “phondflt” owned by jones in thepubs2database on a machine namedHARBORusing port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TD-Njones.phonedflt -Dpubs2

Alternatively, becauseddlgenallows you to use a fully qualified name in the-Nflag, you can omit the-Ddbnameand include the database name in the-Noption:

ddlgen -Ulogin-Ppassword-Sserver:port-TD-Ndbname.owner.defaultname

To generate DDL for all defaults in a database owned by “owner”:

ddlgen -Ulogin-Ppassword-Sserver:port-TD-Nowner.% -Ddbname

Example 3

Databases– Generates DDL for a database calledpubs2on a machine namedHARBORusing port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TDB-Npubs2

If you do not specify adbname,ddlgengenerates DDL for the default database oflogin:

ddlgen -Ulogin-Ppassword-Sserver:port

If you do not use the-Tparameter,ddlgengenerates DDL for a default-type database:

ddlgen -Ulogin-Ppassword-Sserver:port-Ndbname

To generate DDL for all databases:

ddlgen -Ulogin-Ppassword-Sserver:port-TDB-N%

Example 4

Database device– Generates DDL for a database device calledmasterrunning on a machine namedHARBORusing port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TDBD-Nmaster

To generate DDL for all database devices:

ddlgen -Ulogin-Ppassword-Sserver:port-TDBD-N%

Example 5

Temporarydatabases– Generates DDL for all databases, includingtempdb:

ddlgen-Ulogin-Ppassword-Sserver:port-TDB -N%

To generate DDL for alltemporary databases, use theODextended database type:

ddlgen-Ulogin-Ppassword-Sserver:port-TDB -XOD -N%

Although you can use theODextendedtype in Adaptive Server versions 12.5.0.3 and later, versions earlier than12.5.0.3 issue warning messages. You can safely ignore this message;ddlgencontinues processing the command.

To generate DDL for alldatabases except temporary databases, use theOUextended type:

ddlgen-Ulogin-Ppassword-Sserver:port-TDB -XOU -N%

The following generatesDDL for a temporary database namedtempdb1:

ddlgen-Ulogin-Ppassword-Sserver:port-TDB -Ntempdb1

The output includes thefollowing:

·
Acreatetemporary databasestatement

·
create temporary database tempdb1on master = 4,

·
asdas = 2

·
go

·
Ansp_tempdbbindstatement where theisqlapplicationis bound totempdb1:

·
sp_tempdb 'bind','ap', 'isql','DB', 'tempdb1'

·
go

DDL for objects such asviews, stored procedures, and tables is not generated along with DDL for atemporary database because these objects are temporary, and are re-created whenthe server restarts.

When you use the-Fparameterto filter a table while generating DDL for a database object, then indexes,referential integrity, key constraints and triggers automatically get filtered,as they are a subset of the table object.

Example 6

Dumpdevice– generates DDL for a dump device calledtapedump1running on a machine namedHARBORusing port 1955:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TDPD -Ntapedump1

To generate DDL for alldump devices:

ddlgen-Ulogin-Ppassword-Sserver:port-TDPD -N%

Example 7

Executionclass– generates DDL for an execution class called EC2 running on a machinenamedHARBORusing port 1955:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TEC -NEC2

To generate DDL for allexecution classes:

ddlgen-Ulogin-Ppassword-Sserver:port-TEC -N%

Example 8

Enginegroups– generates DDL for an engine group calledLASTONLINErunning on a machine namedHARBORusing port 1955:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TEG -NLASTONLINE

To generate DDL for allengine groups:

ddlgen-Ulogin-Ppassword-Sserver:port-TEG -N%

Example 9

Extendedstored procedures– generates DDL for thexp_cmdshellextended stored procedure in thepubs2database, owned by Jones and runningon a machine namedHARBORusing port 1955, by using the fullyqualifieddbname.owner.extendedstoredprocedureformat with the-Noption:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TXP -Npubs2.jones.xp_cmdshell

Alternatively, you canuse the-Doption instead of using the fullyqualified name:

ddlgen-Ulogin-Ppassword-Sserver:port-TXP

-Nowner.extendedstoredprocedure-Ddbname

To generate DDL for allextended stored procedures:

ddlgen-Ulogin-Ppassword-Sserver:port-TXP -Ndbname.owner.%

Example 10

Filters–Generates DDL for theauthorstable in thepubs2database, filtering for all indexes (I),and referential integrity constraints (RI), primary and unique key constraints(KC), triggers (TR), and partition condition (PC)from the DDL of a table:

ddlgen-Uroy -Proy123 -TU -Nauthors -Dpubs2 -F%

Alternatively, you canspecify each of the filters individually:

ddlgen-Ulogin-Ppassword-TU -Ndbname.owner.table

-FI,RI,KC,TR

The following generatesthe definition oftable_namewhile filtering out foreign keys andprimary-unique keys:

ddlgen-Ulogin-Ppassword-TU -Ntable_name-Ddbname

-FRI,KC

Both of these generateforeign keys for a specified user in the entire database:

ddlgen-Ulogin-Ppassword-TRI -N%.%.% -Ddbname

Or:

ddlgen-Ulogin-Ppassword-TRI -Ndbname%.%.%

Both of these generateDDL for the primary and unique keys of all the tables in a database that beginwith “PK”:

ddlgen-Ulogin-Ppassword-TKC -Ndbname.%.%.PK%

Or:

ddlgen-Ulogin-Ppassword-TKC -N%.%.PK% -Ddbname

The following generatesschema-only definition of a database:

ddlgen-Ulogin-Ppassword-Sserver:port-TF -Ndbname-F%

Alternatively, you canspecify each of the filters individually:

ddlgen-Ulogin-Ppassword-Sserver:port-TDB -Ndbname

-FSGM,GRP,USR,R,D,UDD,V,P,XP,I,RI,KC,TR

The following generatesthe database DDL skipping the compiled object:

ddlgen-Ulogin-Ppassword-Sserver:port-TDB -Ndbname-FTR,D,XP,V,R

The following generatesdatabase definition without a table definition:

ddlgen-Ulogin-Ppassword-Sserver:port-TDB -Ndbname

-FU

Example 11

Groups–Generates DDL for a group called “public” in thepubs2database, running on a machine namedHARBORusing port 1955, by using the fullyqualifieddbname.groupnameformat in the-Noption:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TGRP -Npubs2.public

Alternatively, you canuse the -D option to specify thedbname:

ddlgen-Ulogin-Ppassword-Sserver:port-TGRP -Ngroupname-Ddbname

To generate DDL for allgroups:

ddlgen-Ulogin-Ppassword-Sserver:port-TGRP -Ndbname.%

Example 12

Indexes–Generates DDL for an index calledau_lnamefor the tableauthorsowned by dbo, in thepubs2database:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TI -Ndbo.authors.au_lname -Dpubs2

Alternatively, becauseddlgenallows you to use a fully qualifiedname in the-Nflag, you can omit the-Ddbnameand include the database name in the-Noption:

ddlgen-Ulogin-Ppassword-Sserver:port

-TI -Ndbname.owner.tablename.indexname

If you use a fullyqualified name, you may omit the-Doption.

To generate DDL for allindexes for a single table:

ddlgen-Ulogin-Ppassword-Sserver:port-TI

-Ndbname.owner.tablename.%

To generate DDL for allindexes of all tables in a database:

ddlgen-Ulogin-Ppassword-Sserver:port-TI

-Ndbname.%.%.%

For example, thisgenerates DDL for all indexes for all tables in thepubs2database:

ddlgen-Usa -P -SHARBOR:1955 -TI -Npubs2.%.%.%

ddlgen-Usa -P -SHARBOR:1955 -TI -Npubs2.%.%.%

Example 13

Keys– Bothof these generate DDL for the primary and unique keys of all the tables in adatabase that begin with “PK”:

ddlgen-Usa -P -TKC -Ndbname.%.%.PK%

Or:

ddlgen-Usa -P -TKC -N%.%.PK% -Ddbname

Example 14

Logicalkeys–LKgenerates logical keys of tabledefined bysp_primarykey,sp_commonkey,sp_foreignkeystatements. Since these keys do nothave a name, the name of the object in this case would be the name of thetable. This example generate a DDL for logical keys of table authors indatabase pubs2 running on a machine named HARBOR using port 1955:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TLK -Npubs2.dbo.authors

To generate DDL for alllogical keys in database pub2 use:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TLK -Npubs2.%.%

To filter out logicalkeys definition from DDL of table authors use LK in -F argument, use:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TLK -Npubs2.dbo.authors -FLK

Example 15

Logins–Generates DDL for all logins on a machine namedHARBORusing port 1955:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TL -N%

The password in the DDLgenerated for all logins is “password”.

Alternatively, you canspecify an individual login by using-Nusernameinstead of-N%:

ddlgen-Ulogin-Ppassword-Sserver:port-TL -Nusername

Example 16

Remote Servers–Generates DDL for a remote server calledORANGEon a machine namedHARBORusing port 1955:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TRS -NORANGE

To generate DDL for allremote servers:

ddlgen-Ulogin-Ppassword-Sserver:port-TRS -N%

Example 17

Roles–Generates DDL for the sa_role on a machine namedHARBORusing port 1955:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TRO -Nsa_role

To generate DDL for allroles:

ddlgen-Ulogin-Ppassword-Sserver:port-TRO -N%

The password in the DDLgenerated for all roles is “password”.

Example 18

Rules–Generates DDL for all rules associated withauthorson a machine namedHARBORusing port 1955:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TR -Nauthors.dbo.%

The%symboltellsddlgento createDDLsforall rules that exist on the server.

You can also give thefully qualified name of the rule:

ddlgen-Ulogin-Ppassword-Sserver:port-TR -Ndbname.owner.rulename

Alternatively, you canalso use the-Dparameter:

ddlgen-Ulogin-Ppassword-Sserver:port-TR -Nowner.rulename-Ddbname

Example 19

Segments–Generates DDL using the fully qualifieddbname.segmentnameformat in the-Noptionfor a segment calledlogsegmentfor thepubs2database, on a machine namedHARBORusing port 1955:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TSGM -Npubs2.logsegment

Alternatively, you canuse specify thedbnameusing the-Doption:

ddlgen-Ulogin-Ppassword-Sserver:port-TSGM -Nsegmentname-Ddbname

To generate DDL for allsegments:

ddlgen-Ulogin-Ppassword-Sserver:port-TSGM -Ndbname.%

Example 20

SQLJfunctions–Generates DDL for aSQLJfunction namedregion_ofowned bydboin databasemaster:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TF –Nmaster.dbo.region_of

Alternatively you canalso use the-Dparameter:

ddlgen-Ulogin-Ppassword-Sserver:port-TF –Ndbo.region_of –Dmaster

To generate DDL for allSQLJfunctionsin a database, use object typeF:

ddlgen-Ulogin-Ppassword-Sserver:port-TF –Ndbname.owner.%

Example 21

SQLJprocedures– are akind of stored procedure. You generate DDL forSQLproceduresalong with DDL for stored procedures. The following generates DDL for allstored procedures—includingSQLJprocedures—owned bydboin themasterdatabase:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TP –Nmaster.dbo.%

To generate DDL for allSQLJproceduresthat are only owned bydboin themasterdatabase, use the following, where theextended typeODrefers toSQLJprocedures:

ddlgen-Ulogin-Ppassword-Sserver:port-TP –Nmaster.dbo.% -XOD

To generate DDL for allprocedures exceptSQLJprocedures owned bydboin themasterdatabase, use the following, where theextended typeOUrefers to all stored procedures exceptSQLJprocedures:

ddlgen-Ulogin-Ppassword-Sserver:port-TP –Nmaster.dbo.% -XOU

Example 22

Storedprocedures– Generates DDL for thesp_monitorstored procedure for thepubs2database on a machine namedHARBORusing port 1955, using the fullyqualifieddbname.owner.procedure_nameformat for the-Noption:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TP -Npubs2.dbo.sp_monitor

Alternatively, you canuse specify thedbnameusing the-Doption:

ddlgen-Ulogin-Ppassword-Sserver:port-TP -Nowner.procedurename-Ddbname

To generate DDL for allstored procedures:

ddlgen-Ulogin-Ppassword-Sserver:port-TP -Ndbname.owner.%

Example 23

Tables–Generates DDL for all user tables in thepubs2database owned by “dbo” and running ona machine namedHARBORusing port 1955:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TU -Ndbo.% -Dpubs2

You can also use the-Nparameterto give the fully qualified name of the table:

ddlgen-Ulogin-Ppassword-Sserver:port-TU

-Ndbname.tableowner.tablename

Alternatively, you canalso use the-Dparameter to specify the database:

ddlgen-Ulogin-Ppassword-Sserver:port-TU

-Ntableowner.tablename-Ddbname

To generate DDL for allproxy tables, which uses the valueOD,use-XODinstead, whereXisthe extended type, andODdenotes proxy tables:

ddlgen-Ulogin-Ppassword-Sserver:port-TU

-Ntableowner.% -Ddbname-XOD

To generate DDL for alluser tables, which uses the valueOU,use-XOUinstead, whereXisthe extended type, andOUdenotes user tables:

ddlgen-Ulogin-Ppassword-Sserver:port-TU

-Ntableowner.% -Ddbname-XOU

To generate DDL for alltables, including user tables and proxy tables:

ddlgen-Ulogin-Ppassword-Sserver:port-TU -Ndbname.tableowner.%

Example 24

Triggers–Generates DDL for the triggerchecksumfor thepubs2database on a machine namedHARBORusing port 1955, using the fullyqualifieddbname.owner.trigger_nameformat for the-Noption:

ddlgen-Uroy -Proy123 -SHARBOR:1955 -TTR -Npubs2.dbo.checksum

Alternatively, you canuse specify thedatabase_nameusing the-Doption:

ddlgen-Ulogin-Ppassword-Sserver:port-TTR

-Nowner.triggername-Ddbname

To generate DDL for alltriggers:

ddlgen-Ulogin-Ppassword-Sserver:port-TTR -Ndbname.owner.%

Example 25

User-defined datatypes– Generates DDL for the user-defined datatype “Identype” for thepubs2database on a machine namedHARBORusing port 1955 using the fully qualifieddbname.userdefined_datatypeformat for the-Noption:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TUDD-Npubs2.Identype

Alternatively, you can use the-Doption to specify thedbname:

ddlgen -Ulogin-Ppassword-Sserver:port-TUDD

-Nuserdefined_datatype-Ddbname

To generate DDL for all user-defined datatypes:

ddlgen -Ulogin-Ppassword-Sserver:port-TUDD-Nbname.%

For example, this generates DDL for all indexes for all tables in thepubs2databaseUsage

·
ddlgendoes not identify existing sequences within views, stored procedures or triggers.For this reason, when generating DDL for a database, you must first runddlgenon those views, stored procedures andtriggers that are independent, before runningddlgenon those with dependencies. For example,if view B depends on view A, you must first runddlgenon view A, before running it on view B.

·
The default information forddlgenis:

Option	Parameter	Required	Default
-U	username	Yes	None
-P	password	Yes	None
-S	host_name:port_number	Yes	None
-T	object_type See the-Tparameter description for a list of valid object types	No	Database
-N	object_name	Yes, ifobject_typefor-Tis notDB(database)	Default database name ofusername, if-Tobject_typeisdbor if-Tis not specified
-D	database_name	No	Default database ofusername
-X	extended_object_type Options are: o OU– for user tables, user databases (excluding temporary databases), and stored procedures (excludingSQLJprocedures). o OD– for proxy tables, temporary databases, andSQLJprocedures.	No; use only when theobject_typefor-TisU(user table),P(procedure),DB(database)	None
-O	output_file_name	No	Standard out
-E	error_file_name	No	Standard out
-L	log_file_name	No	None
-V	version_numberofddlgen	No	None

·
At the command line, invokeddlgenusing theddlgenshell script file (ddlgen.batfor Windows), included in your Adaptive Server installation. The mainclass inDDLGen.jariscom.sybase.ddlgen.DDLGenerator.

·
To startddlgenin the Sybase Central plug-in for Adaptive Server:

a.
Right-click on the object for which you want to generate DDL.

b.
SelectGenerate DDL.

·
In the output DDL ofcreatetable, bind statements are generated as independent DLL instead of dependentDLL.

Filters

If you use an invalid filter parameter,ddlgengenerates a warning, ignores thatparameter, and continues with the rest of the valid parameters you specify.

If you specify%along with other filter parameters,ddlgenignores all other filterable parameters, and only shows schema-onlydefinitions.ddlgenthen continues to evaluate the dependencies within the subset of theapplied as the filterable parameters for the database.

Permissions

Sinceddlgenneeds to obtain data from system catalogs,users must either be logged in as “dbo” or haveselectpermissionsonsyscatalogs.