Generating a Script File or Database

Go Up to Using ER/Studio Data Architect

The DDL Generation Wizard lets you generate SQL code to build a database.

Note: For MongoDB you are able to create a JSON file. For this reason, the wizard is slightly different if you are using the DDL generation wizard to create a JSON file.

You can use this wizard to create a database from a physical model. The wizard guides you through the process of building a database and generating the SQL code for the database without requiring you to know any of the underlying commands.

ER/Studio Data Architect lets you generate DDL to update an existing database or to create a brand new database. The wizard lets you select database objects specific to your platform and the options associated with those objects.

Note: The options of the wizard may vary depending on the database platform you are using.

To generate a script file or database:

1. On the Data Model Explorer, right click a physical model, and then select Generate Database.

   Tip: If you select diagram objects on the data model before opening the wizard, the selected objects are selected in the object tree within the wizard, saving you the effort of re-selecting them.

2. Follow the pages of DDL Generation Wizard because they easily guide you through the rest of the process.

Page 1

Generate Script File or Database

• Generate a Single, Ordered Script File: If selected, it writes a single SQL file with specification of all objects.
  • Database Creation Script File: This control allows you to provide the filename and path for the generated SQL file.
• Generate Objects To Multiple Files: If selected, it writes a separate SQL file for each selected object. Objects are ordered in subdirectories for each selected object class.
  • Base Directory: This control allows you to specify the directory that is to contain the generated script files.

Generate Objects with a Database Connection

If selected, it allows you to directly create the objects in a live database. For this purpose:

1. Click Connect
2. In the Database Connection dialog box that appears there are tree available methods to connect.
   • Generate Database Objects Using ODBC: Allows you to select a generic ODBC connection and provide relevant connection information and credentials.
   • Generate Database Objects Using Direct Connect: Allows you to select a native driver connection and provide relevant connection information and credentials.
   • Generate Database Objects Using ER/Studio Team Server: If you are logged in to ER/Studio TS, you can choose a database type, select a data source, and provide credentials. If you are not currently logged in to ER/Studio TS, you are prompted to log in before proceeding.
3. (Optional) Select Create ER/Studio Team Server Data Source from current settings if you want to create the ODBC or native driver data source on ER/Studio Team Server using the setting you have provided to connect.
   • Provide the Name that will identify the data source.
   • Select Relate Data Source to Model, available only for repository models, if you want to relate the current model to the new data source on ER/Studio Team Server.
4. Click Next to continue.

Wizard Quick Launch

See how you can load DDL Generation Wizard settings from a file created on a previous session.

Page 2

Select or Create SQL Server (20xx) Database

Note: Just for SQL Server physical models.

An intermediate page will be shown if your original physical model database platform is Microsoft SQL Server 2000 to 2019. If the database server on which you are deploying the model has multiple database defined you must either select an existing database or create a new one if you want to save your DDL changes into an specific database. For this purpose:

1. Select Select or Create a Database (Optional).
   • Click on Select (use) an Existing SQL Server 20XX Database to update an existing database. Then type the database name.
   • Click on Create a New SQL Server 20XX Database if you choose to create a new one. Then enter the information to configure the new database.
2. Click Next to continue.

Object Selection and Generation Options

The Generation Options are on the tabs where you select the objects in the data model for which you would like to build a database or generate SQL scripts. Below you can see the explanation of some specific objects.

Tables tab

• Generate as primary key constraint: This is one of the Primary Key Options. If you select it, the unique index created will also be listed as a constraint object in the database.
• DROP statements:If you want to generate the Database directly by using the wizard include DROP statements in your Table Generation Options; otherwise, you will receive DDL errors when ER/Studio Data Architect tries to create an object that already exists in the target database.
• Use complete syntax for virtual columns: This option is available for Oracle 11g and later. When this option is not selected, virtual columns are added to the DDL with the following syntax:

column [<datatype>] AS (<column_expression>);

Where <datatype> is set to the data type of the column if the Show Datatype option is on for the column and is omitted otherwise, and <column_expression> is the expression stored as the virtual column expression.

When this is selected, virtual columns are added to the DDL with the following syntax:

 column [<datatype>] GENERATED ALWAYS AS (<column_expression>) VIRTUAL;

• Generate Column Comments: This is one of the Column Options. If you selected it, the information included on the definition tab of columns will appear as a comment on the DDL generated.

Users tab

• Generate system permissions: If you select this options, ER/Studio Data Architect will generate queries to include permissions for the users into your new database. Permissions are added to the DDL with the following syntax:

GRANT [<user_permission>] ON DATABASE TO [<user>];

General Options tab

Note: You can change the object types selected by default in the Generate Other Object Types area of the General Options tab. Go to Tools > Options > Object Types and select your preferences

• Generate Table-level Check Constraints: This type of constraint checks if there is any modification to a row, regardless of the value of the column (changed or not). Is you select that option the Table-level Check Constraints you have added into your model will be generated on the DDL like that:

CONSTRAINT [<constraint_name>] CHECK [<condition>],

• Generate Object Creation Verification Code: Just for the SQL Server and Sybase database platforms. When selected, this option generates code similar to the following for tables, views, indexes, procedures, and functions.

IF OBJECT_ID('Entity1') IS NOT NULL
PRINT '<<< CREATED TABLE Entity1 >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE Entity1 >>>'
go;

Tips:

• If you right-click anywhere on the object tree within the wizard, you can select or deselect all objects using the short-cut menu. You can also display or hide object owner names for those objects with owners.
• To view the DDL generated by the currently selected settings, click the SQL Preview button on the wizard at anytime. You can print or save the SQL script directly from the SQL Preview dialog.

Page 3

Summary

In this page you can check if you have selected all the objects you want to generate on the DDL and the options to do it.

Wizard Quick Launch Options

See how you can save the DDL Generation Wizard settings to a file. You can also rename or delete one you have already created.

See Also

• Database Support and Connectivity
• Connecting to Database Sources and Targets
• Saving and Using Quick Launch Settings
• Exporting an Image of the Data Model
• Printing the Data Model