Use this window to generate DDL statements for your database objects.
DDL (Data Definition Language) refers to the SQL statements that create the objects (tables, views etc) in your database. Many people find it useful to generate and save the DDL for their database objects, as it gives them the ability to recreate their objects on that or another database.
DDL is part of the Administration Component of AQT.
There are two ways to start the DDL function. From the Database Explorer window:
If you have a lot of objects in your database, it can take a few seconds for AQT to retrieve the list of the objects to be displayed in the DDL window.
The Generate DDL function:
When you start the Generate DDL function, you will be shown all the tables (or other objects) within the current schema. You can select the tables you wish to generate the DDL for.
There are a number of options for the way the DDL is generated.
Send Output to
This specifies where the generated DDL is to be written to.
<obj>is specified in this mask. For instance, if AQT is generating the DDL for table dbo.customers,
<obj>.ddlwill create this in a file named
This specifies the character used to delimit the multiple SQL statements in the DDL script.
Include DDL for
This list will contain the different parts of the DDL that can be generated. These will be:
You can select which of these parts of the DDL are to be generated. At least one option can be checked. It is OK to generate DDL for a related object only (eg. for only the indexes and not the tables).
Warning: you should only include the options you need - every option you select will increase the number of queries AQT needs to run against your system tables, and will increase the time to generate the DDL.
Generate foreign key DDL at end of script
By default, the DDL for a foreign key will appear in the DDL script after the DDL for the source table. This can cause a problem when the script is run, as the foreign key cannot be created until after both the source and referenced tables have been created.
Avoid that problem by selecting this option. When this is done, the DDL for the foreign keys is placed at the end of the DDL script.
If you are generating DDL to separate files, the file name for the foreign key DDL will be taken from the File Mask, with
Generate View DDL at end of script
This is similar to the previous option. If you have multi-table views, these cannot be created until after the creation of all the tables that the views are based on. For this reason you may wish to have the view DDL appear at the end of the DDL script.
If you are generating DDL to separate files, the file name for the view will be taken from the File Mask, with
If you have multi-table views, the views may appear more than once in the DDL script.
Generate Trigger DDL at end of script
This is similar to the previous options. If your triggers refer to several tables, these cannot be created until after the creation of all the tables. For this reason you may wish to have the trigger DDL appear at the end of the DDL script.
If you are generating DDL to separate files, the file name for the view will be taken from the File Mask, with trigger replacing
Include commented-out Drop statement
When this option is checked, AQT will include a commented-out drop statement before the definition of each object in your DDL script. It is often useful to have such a drop statement in your scripts, as it makes it easy to drop and redefine your objects.
Include full DDL for related objects
The Include DDL for list contains your main object (eg. table) and all the related objects (indexes, views, triggers etc). When generating the DDL for the related objects the question arises as to how much if their DDL is to be generated. With Include full DDL for related objects is selected, AQT will generate the full DDL for the related objects.
For example: if you are generating the DDL for tablespaces, and you have selected that the DDL for the tables is also to be generated, this option will generate the complete DDL for the tables.
This specifies the database for which the DDL is to be generated. By default this is the database from which you are generating the DDL. You can change this to another database type - AQT will then generate the DDL in the format required for that database.
This is a complex thing for AQT to do - it will need to convert table definitions from the format of one database to the format of another. It will also need to convert data types between different database types. There are limits to how well AQT can do this.
This is used when the DDL is being generated for another type of database (Generate For is specified). This specifies how data types are mapped from the database to the Generate For database.
datetime->timestamp will ensure that datetime columns (in the database) are generated as timestamp columns in the DDL.
You can specify multiple mappings, separated by commas. Example:
Ensure lines are less than 72 bytes
When this option is selected, AQT will ensure that the generated DDL has a maximum line length of 71 bytes. This is useful if you are generating DDL which is to be transferred to a DB2 z/OS system.
This function will split lines at either a blank or a comma, unless these are within a string. If your DDL has large text fields, AQT will split the line in the middle of the text, which may not give correct results.
Format View definitions
When this option is specified, AQT will reformat the view definition into a more readable format. This is the same as using the Format SQL function of the Run SQL window.
Include a Commit after each object
When this option is specified, AQT will include a Commit statement at the end of the DDL for every object.
Use this button to generate the DDL for the objects you have selected. You can cancel the processing by clicking on Abort.
Once you have set up your options, you can save these with File > Save Options. The options will be saved to file on disk. These options include the Include DDL for... items, so will be specific for a type of object.
Once you have save the options, you can load them with File > Open Options.
You can run the DDL as scripting statement. To generate a script statement, use File > View Script Statement. You can copy this statement and use it in the Run SQL window or incorporate this in a batch file.
The script statement will generate the DDL for all objects in your Schema. You can specify a Filter to limit this to a given set of tables.