Schema Scripting Tool

The Schema Scripting Tool allows you to script CREATE and/or DROP SQL statements for selected or all objects in a schema or database. The generated DDL scripts can then be archived or applied in a different environment to re-create the schema or database. In the latter case, you can use the Table Data Compare tool to perform a comparison between the source database and the (empty) destination database, which will create all the INSERT statements to populate the destination database. This way you can effectively copy your entire database, including structure and content, from one environment to another. The Schema Scripting Tool is currently supported for Oracle, DB2, Cassandra, Vertica, Sybase, SQL Server, HSQLDB, PostgreSQL and MySQL. It is available, like all DB Solo features, on all supported OS platforms including Windows, Linux, Mac OSX and Solaris.

Currently the following object types are supported in the Schema Scripting Tool:

• PostgreSQL - Tables, Views, Functions, Sequences
• Vertica - Tables, Views, Functions, Sequences
• MySQL - Tables, Views, Functions, Stored Procedures
• DB2 - Tables, Views, Stored Procedures, Sequences, Synonyms, User Defined Data Types
• Oracle - Tables, Views, Functions, Stored Procedures, Sequences, Synonyms, Collection Types, Object Types, Packages
• Sybase - Tables, Views, Stored Procedures, Rules, User Defined Data Types, Defaults
• SQL Server 2000 - Tables, Views, Functions, Stored Procedures, Rules, User Defined Data Types, Defaults
• SQL Server 2005 and later - Tables, Views, Functions, Stored Procedures, Rules, Synonyms, User Defined Data Types, Defaults
• HSQLDB - Tables, Views, Functions, Stored Procedures, Sequences
• Cassandra - Tables

You can invoke the SQL scripting tool from the Tools-menu or by clicking the appropriate button in the toolbar.

Selecting Objects To Script

The first page of the Schema Scripting Tool lets you select the objects to be included in the script. First you need to select the schema or database from the tree control on the left side of the screen. After clicking on a schema/database on the left side, you will see several tabs on the right side of the screen, one for each supported object type. In each tab you can select individual objects to be selected, by default all objects are selected. The 'Select All' and 'Deselect All' buttons allow you to quickly include all or none of the objects in the corresponding tab. After selecting the objects, click on the Next-button to move to the script settings screen.

Settings

The second screen of the Schema Scripting Tool allows you to configure the output format/content. The screen is divided into two tabs, one contains seneral settings and the other one contains object type-level settings.

General Settings

The Output Destination section of this screen lets you select if the generated DDL statements will be written to a single file or if one file per processed object will be generated. If you selecte the Single File-option, you must enter the name of the output file. If you select the 'File per object' option, you must enter the directory where the script files will be saved. In either case, you can also select how existing files will be handled, wherther the Script Generator will simply write over them or if they should be backed up first.

The Output Format-section lets you select formatting details for the generated scripts. The following options can be configured:

• Keywords - Are the keywords in upper or lower case. This does not apply to the source code of procedures, functions, views, etc
• Statement Separator - How are statements separated from one another
• Show Identifiers - Are identifiers wrapped in quotes or brackets or neither
• Header Section - Is there a header section for each object preceeding the CREATE/DROP statements
• Dependency Sorting - If this option is selected, the CREATE/DROP scripts will be output in the correct dependency order, so that objects that depend on an object will appear after the object they depend on. This option is only available if the Single File-option is selected and if this feature is supported for the DBMS in question
• Statement Types - You can select if DROP or CREATE statements, or both, are generated.

Object Level Settings

This screen contains a tab for each supported object type. Here you can configure settings that apply to the particular object type. All object types have the following settings:

• Script Object - Are objects of this type included in the generated script at all
• Include Object Privileges - Are the GRANT statements included in the generated CREATE script
• Output File - Name of the output file for this object type. This setting is not used if you selected the 'Single File' option under the General Settings tab. The name can contain tokens that will be replaced with actual values when the script is generated. The following tokens are supported:

   

  Token	Meaning
  #name#	Name of the object (lowercase)
  #NAME#	Name of the object (uppercase)
  #schema#	Name of the schema/owner of the object (lowercase)
  #SCHEMA#	Name of the schema/owner of the object (uppercase)
  #dd#	Current day of the month (1-31)
  #mm#	Current month of the year (1-12)
  #yyyy#	Current year

Once you have configured all the settings, click on the Next-button to start the schema scripting process. While the schema scripting tool is generating the script for you, you can monitor the status in the progress dialog.

Results

In the last screen of the schema scripting tool you can view the resulting script(s). The upper part of the screen contains a list of files that were generated. If you selected the 'Single File' option on the settings screen, the upper panel will only contain one file, otherwise it will contain several files (if you selected more than one object to be scripted). Both the file name and its size in bytes is shown in the grid.

By clicking on the files in the results grid, you can bring up their contents in the lower part of the screen. The file contents panel allows you to save the SQL script to a different file, print the script or copy the script to the system clipboard. Once you are satisfied with the generated script, click on the Close-button to exit the Schema Scripting Tool and go back to the main application.