DatabaseSpy enables you to generate DDL (Data Definition Language) statements for all objects of a database or schema, as one SQL script. Generating the complete DDL scripts of a database or schema may be useful, for example, if you want to re-create the structure of that schema or database on another server or environment, or if you want to back up the current database structure, or if you want to create a text snapshot of the current database structure for comparing it later to another snapshot.
To generate complete DDL scripts from a schema or database:
1.In Online Browser, identify the folder that is the immediate parent of all "Tables", "Views", "Stored Procedures", etc folders of the current database or schema. For example, if you want to generate the complete DDL scripts of the "dbo" schema in the ZooDB tutorial database, this folder is "dbo". For more information about creating the tutorial ZooDB database, see Prerequisites.
If the current database kind supports multiple schemas (like the ZooDB database above), it is possible to generate DDL scripts for multiple schemas in one operation (each schema in a separate file). To select multiple schemas, click the required schemas while holding the Shift key pressed.
Note that, in this example, the immediate parent of all tables, views, procedures etc is a schema. However, it may be the root database object (and thus have a different appearance) in other database kinds (since the structure displayed in the Online Browser depends on the kind of the currently connected database).
2.Right-click the folder identified in step 1 ("dbo", in this example), and select the command Show in new SQL Editor | Complete DDL Script from the context menu.
When you run the Complete DDL Script command, DatabaseSpy analyzes all database objects (tables, views, stored procedures, and so on), and then generates their corresponding DDL statements in a new SQL Editor window. If multiple schemas were selected, the corresponding DDL scripts will be in a new SQL Editor window. While DDL statements are being generated, you are informed of the progress in the Output window as well as through a dialog box from where you can also cancel the operation.
Once DDL statement generation is complete, you can save the script to a file (see Opening, Saving, and Closing SQL Files).