Chat now with support
Chat with Support

Toad Data Modeler 7.0 - User Guide

Introduction User Interface Models and Model Objects
Physical Data Model
Entity Relationship Diagram Objects Basic Database Design Advanced Database Design
Universal Data Model Logical Data Model Working with Model Objects
Features and Tools
Application Variables Export/Import DDL Script Generation Graphics Model Actions Print Create New Project Reports Reverse Engineering Scripting and Customization About Templates Tips and Tricks Toad for Oracle Integration Toad Intelligence Central (TIC) Integration Tools Version Control
Options and Configuration Databases
Amazon Redshift 1.0 IBM DB2 LUW 9.5 IBM DB2 LUW 9.7 IBM DB2 LUW 10.1 IBM DB2 LUW 10.5 IBM DB2 LUW 11.1 IBM DB2 z/OS 10 IBM DB2 z/OS 11 Greenplum 4.1 Greenplum 4.2 Ingres 9.3 Ingres 10.0 EDB Postgres Advanced Server 10 Microsoft Access 2007/2010 Microsoft Azure SQL Database V12 Microsoft SQL Server 2005 Microsoft SQL Server 2008 Microsoft SQL Server 2012 Microsoft SQL Server 2014 Microsoft SQL Server 2016 Microsoft SQL Server 2017 MySQL 5.0 MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0 Oracle 10g Oracle 11g Release 1 Oracle 11g Release 2 Oracle 12c Release 1 Oracle 12c Release 2 Oracle 18c Specifics - Oracle 12c Release 2 PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 PostgreSQL 10 SQLite 3.7 Sybase ASE 15.5 Sybase ASE 15.7 SAP ASE 16.0 Sybase IQ 15.2 Sybase SQL Anywhere 11 SAP SQL Anywhere 17 Teradata 13 Vertica Database 8.0
Copyright Legal Notices

DDL Script Generation

Script Generation Preparation

Before generating a DDL script, you might want to configure the following things:

Order of Generated Objects

Toad Data Modeler allows you to set the order of objects before SQL/DDL script generation.

Example: You want to generate Users before User Permissions to a table.

You can change order of the following objects:

  • Domains
  • Entities
  • Views
  • Dictionary types
  • Sequences
  • Stored procedures
  • Functions
  • Users
  • User data types

To set an order of objects for SQL/DDL script generation

  1. Go to Model Menu | Order of Generated Objects.
  2. Select an object, or an object type.
  1. Use to move your selection up/down by one step or use drag&drop to place your selection wherever you want.

Note: To display full names of objects, click on

  1. Switch to Extension tab for more options. Press F2 or left-click and hold any of the values to set it as desired.

You can configure when should be the listed object types generated.

To sort objects automatically according to their relationships

  1. Select Model | Order of Generated Objects to set the desired order of entities
    1. Select Move Parent Entity before Child Entity - lists each parent entity before their respective child entities or
    2. Select Move Child Entity after Parent Entity - lists all child entities after their parent entities

Note: When you set After CREATE TABLE Section value for an object, the object will not be shown in Entity SQL Preview.

Order of Generated Objects dialog is also accessible from DDL Script Generation dialog | Detail Settings tab| Edit.

Script Encoding

To set encoding for generated scripts

  1. Go to Settings Menu | Options | Model | Physical Model | SQL Generation Script tab | SQL Script section.
  2. Choose the preferred encoding from Encoding Used for SQL Scripts box.

How to Generate DDL Script

This topic describes full configuration of a DDL script generation. In most cases, you will not need to configure all of the available settings.

Note: An Oracle 10g model is used in this topic. The dialogs and options available may vary depending on your model database platform and version.

Start by opening DDL Script Generation dialog using any of these methods:

  • Click the button on Model Toolbar
  • Go to Model Menu | Generate DDL Script | Run
  • Use shortcut - F9

Selecting Items for Generation

This essential part of script generation, you need to select items which should be generated.

Option Description
Location of SQL File Select the location where the generated script will be saved. You can also select an already existing script and check Append to File checkbox to append the generated code to the original file.
User/Schema Choose a user/schema from this box to only generate all of its assigned items.
Selection You can save/load your custom selections. To do that, simply select the objects you want to be generated from the Objects Grid and then click button to save your selection. If you will generate scripts in the future, you can load your selection or even make it default .
Bulk Selection

This section contains several buttons that help you select multiple objects quickly. You can:

  • Select All
  • Deselect All
  • Invert Selection
  • Auto Check (if enabled, automatically checks sub-items when the parent item is checked)

There is also a combobox which can be used to assign Extended Value to all items in Objects Grid at once.

Objects Grid In this grid you select objects you want to generate by checking them in the Property Name column. And in the Extended Value column you can choose the SQL statement which should be used for the specific item in the final script (e.g. create, replace, drop).

Detailed Settings

Specific and database related settings can be found on this tab. Usually it is not necessary to change them in any way.

Referential Integrity

The main selection of objects for generation takes place in Select List. Referential integrity further specifies which relations are generated. It has the following options:

  • Always - relationships are always generated if they are included in Select List
  • If Child Table is Generated - relationship is generated only when its child table is included in Select List
  • If Parent Table is Generated - relationship is generated only when its parent table is included in Select List
  • If Child or Parent Table is Generated - relationship is generated only when child, parent or both are included in Select List
  • If Child and Parent Tables are Generated - relationship is generated only when both child and parent tables are included in Select List

Select List

If you want to generate only a set of specific model objects, you can do exactly that on Select List tab. By default, all objects that have Generate checkbox checked (e.g. Entity Properties) will be generated (unless you edited some other setting). If you uncheck the Generate by Property "Generate", you are able to select any objects you want.

Note: This tab is linked to the What to Generate tab | Objects Grid. If you uncheck an object type there, it you won't be able to select any of its objects in Select List tab.

Script Preview

You can preview the generated SQL script at any time. To do that, check the Show Preview checkbox on What to Generate tab. Then click the Generate button. Toad Data Modeler will offer you a preview of the script before you decide to save it. If you wouldn't have checked the Show Preview checkbox, the script would be saved first before you could preview it.

Note: Models are automatically verified before generating DDL script. To learn more, see Model Verification.


TIP: If you plan to do this action again in the future, you might want to click the Save Action button to save this Action Definition. See Model Actions for more information.

Example of Generated Script

Videorental Model

Oracle 10g database/* 
Created: 15.3.2007
Modified: 6.12.2013
Project: Videorental Project
Model: Videorental
Company: Quest Software Inc.
Author: Jan Novak
Database: Oracle 10g
        */
        -- Create user data types section -------------------------------------------------
        CREATE TYPE SCOTT.Phone_List_TypeAS VARRAY(5) OF VARCHAR2(25)/
        CREATE TYPE SCOTT.Cust_Address_TypeAS OBJECT
        ( street_address     VARCHAR2(40)
        , postal_codeVARCHAR2(10)
        , cityVARCHAR2(30)
        , state_province     VARCHAR2(10)
        , country_idCHAR(2)
        , phonePhone_List_Type
        )/
        CREATE TYPE SCOTT.Price_TypeAS OBJECT (price NUMBER(10,2),
        MEMBER FUNCTION total_price_VAT (vat number) RETURN NUMBER)/
        CREATE TYPE BODY SCOTT.Price_TypeISMEMBER FUNCTION total_price_VAT (vat NUMBER)
        RETURN NUMBER ISBEGINRETURN (price*((vat+100)/100));
        END;
        END;/
        -- Create sequences section -------------------------------------------------
        CREATE SEQUENCE SCOTT.SeqExemplarINCREMENT BY 1START WITH 1NOMAXVALUENOMINVALUENOCACHE/
        -- Create tables section -------------------------------------------------
        -- Table SCOTT.T_CUSTOMERCREATE TABLE SCOTT.T_CUSTOMER(
        customer_id Integer NOT NULL,
        name Varchar2(20 CHAR),
        address SCOTT.Cust_Address_Type
        )/
        -- Create indexes for table SCOTT.T_CUSTOMERCREATE INDEX i_name ON SCOTT.T_CUSTOMER (name)/
        -- Add keys for table SCOTT.T_CUSTOMERALTER TABLE SCOTT.T_CUSTOMER ADD CONSTRAINT pk_T_CUSTOMER PRIMARY KEY (customer_id)/
        -- Table and Columns comments sectionCOMMENT ON COLUMN SCOTT.T_CUSTOMER.name IS 'Name column can contain First and Middle name. Surname must be in different column.'/
        -- Table SCOTT.T_GENRECREATE TABLE SCOTT.T_GENRE(
        genre_id Integer NOT NULL,
        name Varchar2(20 CHAR)
        )/
        -- Add keys for table SCOTT.T_GENREALTER TABLE SCOTT.T_GENRE ADD CONSTRAINT pk_T_GENRE PRIMARY KEY (genre_id)/
        -- Table SCOTT.T_MEDIUMCREATE TABLE SCOTT.T_MEDIUM(
        medium_id Integer NOT NULL,
        medium_type Varchar2(20 BYTE)
        )/
        -- Add keys for table SCOTT.T_MEDIUMALTER TABLE SCOTT.T_MEDIUM ADD CONSTRAINT pk_T_MEDIUM PRIMARY KEY (medium_id)/
        -- Table SCOTT.T_EXEMPLARCREATE TABLE SCOTT.T_EXEMPLAR(
        exemplar_id Integer NOT NULL,
        film_id Integer NOT NULL,
        medium_id Integer NOT NULL,
        price_per_day Integer
        )/
        -- Add keys for table SCOTT.T_EXEMPLARALTER TABLE SCOTT.T_EXEMPLAR ADD CONSTRAINT pk_T_EXEMPLAR PRIMARY KEY (exemplar_id)/
        ALTER TABLE SCOTT.T_EXEMPLAR ADD CONSTRAINT ak_exemplar_ppd UNIQUE (exemplar_id,price_per_day)/
        -- Table SCOTT.T_FILMCREATE TABLE SCOTT.T_FILM(
        film_id Integer NOT NULL,
        title Varchar2(50 CHAR) NOT NULL,
        director Varchar2(30 CHAR) NOT NULL,
        production_company Varchar2(50 CHAR),
        genre_id Integer NOT NULL,
        min_age Integer,
        film_ID_episodes Integer
        )/
        -- Add keys for table SCOTT.T_FILMALTER TABLE SCOTT.T_FILM ADD CONSTRAINT pk_T_FILM PRIMARY KEY (film_id)/
        ALTER TABLE SCOTT.T_FILM ADD CONSTRAINT ak_title_director UNIQUE (title,director)/
        -- Table and Columns comments sectionCOMMENT ON COLUMN SCOTT.T_FILM.production_company IS 'Company name must contain also information about company type - LTD, Inc. and so on.'/
        -- Table SCOTT.T_BORROWINGCREATE TABLE SCOTT.T_BORROWING(
        exemplar_id Integer NOT NULL,
        customer_id Integer NOT NULL,
        start_date Date DEFAULT sysdate,
        end_date Date,
        total_price SCOTT.Price_Type,
        VAT Number(4,2) DEFAULT 19,
        CONSTRAINT check_end_after_start CHECK ((end_date>start_date) or (end_date is null))
        )/
        -- Add keys for table SCOTT.T_BORROWINGALTER TABLE SCOTT.T_BORROWING ADD CONSTRAINT pk_T_BORROWING PRIMARY KEY (exemplar_id)/
        -- Create triggers for table SCOTT.T_BORROWINGCREATE TRIGGER SCOTT.tri_BORROWINGBEFORE INSERTON SCOTT.T_BORROWINGFOR EACH ROWdeclare price number(10,2);
        beginselect price_per_dayinto pricefrom T_EXEMPLARwhere T_EXEMPLAR.exemplar_id = :new.exemplar_id;
        :new.total_price := Price_Type((:new.end_date-:new.start_date)*price);
        end;/
        CREATE TRIGGER SCOTT.tru_BORROWINGBEFORE UPDATEON SCOTT.T_BORROWINGFOR EACH ROWdeclareprice number(10,2);
        total_price_old number(10,2);
        end_d date;
        start_d date;
        beginselect e.price_per_dayinto pricefrom T_EXEMPLAR ewhere e.exemplar_id = :new.exemplar_id;
        total_price_old := :new.total_price.price;
        end_d := :new.end_date;
        start_d := :new.start_date;
        if (total_price_old is null) thentotal_price_old := 0;
        end if;
        if ((end_d-start_d)*price != total_price_old) then
        :new.total_price := Price_Type((end_d-start_d)*price);
        end if;
        end;/
        -- Table and Columns comments sectionCOMMENT ON TABLE SCOTT.T_BORROWING IS 'Borrowed items'/
        -- Table SCOTT.T_ORDER_RECORDCREATE TABLE SCOTT.T_ORDER_RECORD(
        customer_id Integer NOT NULL,
        film_id Integer NOT NULL,
        order_date Date
        )/
        -- Add keys for table SCOTT.T_ORDER_RECORDALTER TABLE SCOTT.T_ORDER_RECORD ADD CONSTRAINT pk_T_ORDER_RECORD PRIMARY KEY (customer_id,film_id)/
        -- Table and Columns comments sectionCOMMENT ON TABLE SCOTT.T_ORDER_RECORD IS 'All records are stored in list of records. It will be possible to book a movie.'/
        -- Table SCOTT.T_CUSTOMER_RATINGCREATE TABLE SCOTT.T_CUSTOMER_RATING(
        title Varchar2(50 CHAR) NOT NULL,
        director Varchar2(30 CHAR) NOT NULL,
        rating Integer DEFAULT 3CONSTRAINT check_rating CHECK (rating > 0)
        )/
        -- Add keys for table SCOTT.T_CUSTOMER_RATINGALTER TABLE SCOTT.T_CUSTOMER_RATING ADD CONSTRAINT pk_T_CUSTOMER_RATING PRIMARY KEY (title,director)/
        -- Table and Columns comments sectionCOMMENT ON TABLE SCOTT.T_CUSTOMER_RATING IS 'Movie ratings (by customers)'/
        -- Create procedures section -------------------------------------------------
        CREATE PROCEDURE SCOTT.p_Customer_Has_Num_Film(cid IN integer, num OUT integer)
        ASBEGINSELECT count(*)
        INTO numFROM T_CUSTOMER c, T_BORROWING b, T_EXEMPLAR e, T_FILM fWHERE c.customer_id=cidand c.customer_id=b.customer_idand b.exemplar_id=e.exemplar_idand e.film_id=f.film_id;
        END;/
        -- Create views section -------------------------------------------------
        CREATE VIEW SCOTT.v_Customer_Has_Film ASSELECT DISTINCT c.name, c.address.city AS city, f.title, f.directorFROM SCOTT.T_CUSTOMER c, SCOTT.T_BORROWING b, SCOTT.T_EXEMPLAR e, SCOTT.T_FILM fWHERE c.customer_id=b.customer_idand b.exemplar_id=e.exemplar_idand e.film_id=f.film_id/
        -- Create functions section -------------------------------------------------
        CREATE FUNCTION SCOTT.f_Customer_Has_Num_Film(cid IN integer)
        RETURN integerISsol integer;
        BEGINp_Customer_Has_Num_Film(cid,sol);
        RETURN (sol);
        END;/
        -- Trigger for sequence SCOTT.SeqExemplar for column exemplar_id in table SCOTT.T_EXEMPLAR ---------
        CREATE OR REPLACE TRIGGER SCOTT.ts_T_EXEMPLAR_SeqExemplar BEFORE INSERTON SCOTT.T_EXEMPLAR FOR EACH ROWBEGINSELECT SCOTT.SeqExemplar.nextval INTO :new.exemplar_id FROM DUAL;
        END;/
        CREATE OR REPLACE TRIGGER SCOTT.tsu_T_EXEMPLAR_SeqExemplar AFTER UPDATE OF exemplar_idON SCOTT.T_EXEMPLAR FOR EACH ROWBEGINRAISE_APPLICATION_ERROR(-20010,'Cannot update column exemplar_id in table SCOTT.T_EXEMPLAR as it uses sequence.');
        END;/
        -- Create relationships section -------------------------------------------------
        ALTER TABLE SCOTT.T_BORROWING ADD CONSTRAINT makes FOREIGN KEY (customer_id) REFERENCES SCOTT.T_CUSTOMER (customer_id)/
        ALTER TABLE SCOTT.T_BORROWING ADD CONSTRAINT is_related_to FOREIGN KEY (exemplar_id) REFERENCES SCOTT.T_EXEMPLAR (exemplar_id)/
        ALTER TABLE SCOTT.T_ORDER_RECORD ADD CONSTRAINT places FOREIGN KEY (customer_id) REFERENCES SCOTT.T_CUSTOMER (customer_id)/
        ALTER TABLE SCOTT.T_EXEMPLAR ADD CONSTRAINT is_available_on FOREIGN KEY (medium_id) REFERENCES SCOTT.T_MEDIUM (medium_id)/
        ALTER TABLE SCOTT.T_ORDER_RECORD ADD CONSTRAINT is_required_by FOREIGN KEY (film_id) REFERENCES SCOTT.T_FILM (film_id)/
        ALTER TABLE SCOTT.T_FILM ADD CONSTRAINT is_of FOREIGN KEY (genre_id) REFERENCES SCOTT.T_GENRE (genre_id)/
        ALTER TABLE SCOTT.T_EXEMPLAR ADD CONSTRAINT has FOREIGN KEY (film_id) REFERENCES SCOTT.T_FILM (film_id)/
        ALTER TABLE SCOTT.T_CUSTOMER_RATING ADD CONSTRAINT is_rated FOREIGN KEY (title, director) REFERENCES SCOTT.T_FILM (title, director)/
        ALTER TABLE SCOTT.T_FILM ADD CONSTRAINT has_more_episodes FOREIGN KEY (film_ID_episodes) REFERENCES SCOTT.T_FILM (film_id)/
        -- Grant permissions section -------------------------------------------------
        insert into T_GENRE values (1,'crime')/
        insert into T_GENRE values (2,'western')/
        insert into T_GENRE values (3,'drama')/
        insert into T_GENRE values (4,'biography')/
        insert into T_GENRE values (5,'comedy')/
        insert into T_MEDIUM values (1,'CD - DivX')/
        insert into T_MEDIUM values (2,'CD - VideoCD')/
        insert into T_MEDIUM values (3,'DVD')/
        insert into T_MEDIUM values (4,'Videotape')/
        insert into T_FILM values (1,'The Shawshank Redemption','Frank Darabont','Castle Rock Entertainment',1,15, null)/
        insert into T_FILM values (2,'The Godfather','Francis Ford Coppola','Paramount Pictures',1,15,null)/
        insert into T_FILM values (3,'The Godfather: Part II','Francis Ford Coppola','Paramount Pictures',1,15,2)/
        insert into T_FILM values (4,'The Good, the Bad and the Ugly','Sergio Leone','PEA',2,15,null)/
        insert into T_FILM values (5,'Pulp Fiction','Quentin Tarantino','Miramax Films',1,18,null)/
        insert into T_FILM values (6,'12 Angry Men','Sidney Lumet','Orion-Nova Productions',3,15,null)/
        insert into T_FILM values (7,'Schindler''s List','Steven Spielberg','Universal Pictures',4,15,null)/
        insert into T_FILM values (8,'One Flew Over the Cuckoo''s Nest','Milos Forman','Fantasy Films',3,15,null)/
        insert into T_EXEMPLAR values (1,1,3,3)/
        insert into T_EXEMPLAR values (2,1,3,3)/
        insert into T_EXEMPLAR values (3,1,3,3)/
        insert into T_EXEMPLAR values (4,1,4,2)/
        insert into T_EXEMPLAR values (5,1,4,2)/
        insert into T_EXEMPLAR values (6,2,3,3)/
        insert into T_EXEMPLAR values (7,2,4,2)/
        insert into T_EXEMPLAR values (8,3,3,3)/
        insert into T_EXEMPLAR values (9,4,4,2)/
        insert into T_EXEMPLAR values (10,5,3,3)/
        insert into T_EXEMPLAR values (11,6,4,2)/
        insert into T_EXEMPLAR values (12,6,4,2)/
        insert into T_EXEMPLAR values (13,7,4,2)/
        insert into T_CUSTOMER values (1,'Audrey',Cust_Address_Type('92A Campton Avenue','60021','Fox River Grove',null,'us',null))/
        insert into T_CUSTOMER values (2,'Simon',Cust_Address_Type('12 Hillpeak Street','60006','Arlington Heights',null,'ca',null))/
        insert into T_CUSTOMER values (3,'Dave',Cust_Address_Type('31D South Avenue','60001','Alden',null,'us',Phone_List_Type('541 123 456')))/
        insert into T_CUSTOMER values (4,'Chris',Cust_Address_Type('5 Quiet Street','60061','Vernon Hills',null,'us',Phone_List_Type('541 123 456','596 815 641')))/
        insert into T_CUSTOMER values (5,'Elen',Cust_Address_Type('47 My Avenue','60083','Beach Park','Illinois','us',null))/
        insert into T_BORROWING (exemplar_id,customer_id,start_date,end_date) values (11,1,to_date('01-01-2010','DD-MM-YYYY'),to_date('03-01-2010','DD-MM-YYYY'))/
        insert into T_BORROWING (exemplar_id,customer_id,start_date,end_date) values (12,2,to_date('02-01-2010','DD-MM-YYYY'),to_date('10-01-2010','DD-MM-YYYY'))/
        insert into T_BORROWING (exemplar_id,customer_id,start_date) values (1,2,to_date('06-01-2010','DD-MM-YYYY'))/
        insert into T_BORROWING (exemplar_id,customer_id,start_date) values (6,4,to_date('07-01-2010','DD-MM-YYYY'))/
        insert into T_BORROWING (exemplar_id,customer_id,start_date) values (7,3,to_date('07-01-2010','DD-MM-YYYY'))/
        insert into T_BORROWING (exemplar_id,customer_id) values (9,3)/
        insert into T_ORDER_RECORD values (1,2,to_date('01-01-2010','DD-MM-YYYY'))/
        insert into T_ORDER_RECORD values (1,1,to_date('01-01-2010','DD-MM-YYYY'))/
        insert into T_ORDER_RECORD values (2,2,to_date('03-01-2010','DD-MM-YYYY'))/
        insert into T_ORDER_RECORD values (3,3,to_date('06-01-2010','DD-MM-YYYY'))/
			
Related Documents