10.3 Generate SQL Scripts using ADS

Aqua Data Studio’s Schema Script Generator generates SQL Scripts for schema objects in a database by producing a singel file or files per object. Existing schema objects in the Schema Browser can be converted to DDL by the Script Generator with the Schema Script Generator.

The Schema Script Generator differs from the Server Script Generator in that Schema Scripts are for schema objects such as tables and procedures, while Server Scripts are for database management of server objects like backups, alerts, logins, roles and tabelspaces.

The Schema Script Generator can also generate syntax highlighted scripts in a preview window for copy and paste. The steps to Generate Schema Scripts are described in the steps below.

1. Launch the Script Generator dialog by Selecting Tools->Script Generator from the Menu Bar Selecting Tools->Script Generator from the right-click pop-up menu on a database object in the Schema Browser.

2. Select a Database and Schema (or All Schemas). In the left panel, select one or more types of objects to be scripted. Click the green check button to select all, the red X button to de-select all, or the red/green button to revert the current selection. All objects matching the selected schema & object types will appear in the right panel. Select one or more objects to be scripted. The quickfilter can be used to select objects via wildcards. Click the green check button to select all, the red X button to de-select all, or the red/green button to revert the current selection. Click Next.

3. Here in the Options Tab specify where to save the generated script or scripts. Save as One File so that all SQL Statements will be output to a single file. Save as One File Per Object so that SQL statements will be output to one file per object, or Preview the SQL so that all the SQL statements will be output to the Preview tab for copy and paste. The directory and file name, encoding and platform of the file can be set. Options do vary based on database vendor and version. Select any appropriate options for:

• Statement Separator - GO, /, ; or enter your own separator. For reuse in Aqua Data Studio, GO is the prefered separator.
• Object Quoted Identifier - none, [ ], "" or ’’
• Generate the CREATE - generate CREATE statements for the selected objects.
• Generate the DROP - generate DROP statements for the selected objects.
• Include Descriptive Header - include a header at the top of the file indicating the date, time, database, schema and names of objects contained in the file.
• Sort By Dependencies - Not available on MySQL - Output the objects in order to avoid dependency conflicts.
  e.g. if object A depends on object B, object B should be output before object A
• Include Object Permissions - Allows inclusion of DDL permissions
• Enable Identity Insert - SQL Server / Sybase only - If a table contains identity columns, place delimiting keywords to allow INSERT of data into these columns.
• Include Table Storage - DB2 -Scripts the table storage and buffer information.
• Include schema object comments as script comments - DB2 - If there are schema object comments write them out as script comments.
• Tables: Script full table (Indexes, Triggers) - Output indexes and triggers for tables.
• Include INSERT statements for data of Tables - Output data of Table objects as INSERT statements.
• Qualify Objects - Fully qualify names of objects by including the Schema name.
• Include blank line between statements - Include blank lines to improve format of output.
• Overwrite Existing Script(s) - When scripting one object per file, allows to overwrite current scripts instead of generating enumerated script names
• Use specific procedure name for file name - DB2 only - enable/disable using specific procedure names as file names when saving procedure scripts into individual files. As DB2 can have multiple procedures with the same name, specific file names should be used together with real procedure names when naming individual script files. However, if a user is sure that there are no procedures with the same name being scripted, then he/she can use real procedure names and disable the option.

4. Click Next to generate the scripts for the selected objects. The Status tab displays any warnings or messages as the export progresses. The output process can be halted at any point by clicking Cancel.

5. If Preview was selected as the destination of the export, the Preview Tab displays the export with syntax highlighting for copy and paste use.

Click Close to close the dialog or click Previous to change selections and regenerate the script(s).