Setting preferences for DDL script generation

Use the Code Template page in the Preferences window to set preferences for adding SQL statements at the beginning or the end of DDL scripts that are generated by the workbench.

Adding SQL statements to the DDL scripts that are generated by the workbench is useful if you commonly require SQL statements to run that are not supported by the underlying SQL model. For example, you might want to always include GRANT or REVOKE statements before or after the DDL statements. When you set the statement syntax in the Preferences window, these statements are automatically added to the generated DDL scripts so that you do not need to modify the DDL script manually to add these statements.

The following scenario describes an example of how the code template preferences can be used.
  1. Set the following prolog pattern for CREATE TABLE statements:

    SET CURRENT SQLID = {schema}

  2. Set the following postlog patterns for CREATE TABLE statements:

    SELECT, UPDATE, INSERT ON TABLE {table} TO PUBLIC

  3. Set the following Set the following prolog pattern for DROP VIEW statements:

    GRAND DELETE ON TABLE {view} TO {schema}

The DDL script is generated as follows:
GRAND DELETE ON TABLE VIEW1 TO RTESCHMA;

DROP VIEW VIEW1;

DROP TABLE TABLE1;

SET CURRENT SQLID = RTESCHMA;

CREATE TABLE TABLE2 (
		C1 INTEGER,
			C2 INTEGER
		);

GRAND SELECT, UPDATE, INSERT ON TABLE TABLE2 TO PUBLIC;

CREATE VIEW VIEW1 AS
 		(SELECT C2 FROM RTESCHMA.TABLE2)

To set preferences for DDL generation:

  1. Click Window > Preferences from the main menu.
  2. Expand the Data node to see available options. If you do not see this node, click Workbench > Capabilities in the Preferences window and enable the Data capabilities.
  3. Click Code Template.
  4. Modify the settings.
    1. Highlight an object in the CREATE and DROP patterns list.
    2. Type SQL statements for that object in the Prolog pattern or Postlog pattern fields. Statements in the Prolog pattern field are added to the top of generated DDL scripts. Statements in the Postlog pattern field are added to the bottom of generated DDL scripts.
    3. To add variables, click Insert Variable and select from predefined variables. Variables are replaced by the corresponding string when the pattern is applied to an object.
    4. To enable the code templates that you specified, check the check box next to the object in the CREATE and DROP patterns list.
  5. Click OK.
The next time you generate a DDL script, the template statement patterns that you specified are added to the script.

Feedback