Generating Complete DDL Scripts
DatabaseSpy enables you to generate DDL (Data Definition Language) statements for all objects of a database or schema, as one SQL script. You can also generate DDL scripts for multiple schemas or databases in the same operation.
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, select the database or schema from which you would like to generate DDL scripts. In the example below, the "dbo" schema of the "ZooDB" tutorial database is selected.
2.Right-click the schema or database ("dbo", in this example), and select the command Show in new SQL Editor | Complete DDL Script from the context menu.
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 or databases, click the required schemas while holding the Shift key pressed.
Note that, in this example, the immediate parent of all tables, views, procedures, and so on, is a schema. This may be a database in other database kinds. The structure displayed in the Online Browser depends on the kind of the currently connected database.
When you run the Complete DDL Script command for a single schema or database, DatabaseSpy analyzes all objects (such as tables, views, stored procedures, and so on), and then generates their corresponding DDL statements as a single script, in a new SQL Editor window. If multiple schemas or multiple databases were selected, the DDL script of each schema or database will be in a new SQL Editor window.
In SQL Server, if you selected both a schema and a database, then scripts for the database will be generated. The generated database script will include all objects from all schemas in that database.
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, as described in Opening, Saving, and Closing SQL Files.