Toad Data Modeler Scripts

Description

The script will go through all FK attributes and will automatically select the checkbox Override Identity. After you execute the script, take a look at the Log window where all entities and attributes where the checkbox was selected will be written out.

You can use the script provided that you set autoincrement in a domain, use the domain in PK attribute and create relationship to another entity.  In this case, FK attribute with the domain (and identity) will be created in child entity. However, you need to override the identity. Not to do it for each FK attribute individually, you can run this script.

How to Execute Script:

1. Unzip the attachment.
2. Copy and paste the script in the Scripting Window in Toad Data Modeler.
3. Run the script. (See the Execute button in the Scripting Window toolbar.)

Note:

var Model = app.Models.GetObject(0);
… parameter in GetObject determines with which model the script should work. 0 = first model listed in the Application View, 1 = second model listed in the Application View etc.

Database: MS SQL Server 2005 and MS SQL Server 2000
Size: 1 KB
Date: 16 November 2007
Author: Mario

Prefix defined on the third line of the script will be added to every entity, index and trigger. The prefix will be added only once, which means that if you add another entity to your model later and run the script then, the prefix will be added only to this newly created entity.

You can use the script if you like to add a prefix to every entity, index and trigger of your model.

If you need to add prefix to other objects of your model, feel free to modify the script. For this purpose, we recommend you to have a look at the TDM3 Reference document that is part of the Documentation package of TDM3. (See the Help menu | Reference.)

How to Execute Script:

1. Unzip the attachment.
2. Open it e.g. in Notepad.
3. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window).
4. Run the script. (See the Execute button in the Scripting Window toolbar.)

Note:

var Model = app.Models.GetObject(0);
… parameter in GetObject determines with which model the script should work. 
0 = first model listed in the Application View, 1 = second model listed in the Application View etc.

Database: All supported databases.
Size: 3 KB
Date: 20 November 2007
Author: Mario

The script will add a new domain to your model.

How to Execute Script:

1. Unzip the attachment.
2. Open it e.g. in Notepad.
3. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window).
4. Run the script. (See the Execute button in the Scripting Window toolbar.)

Notes:

1. var Model = app.Models.GetObject(0); 

   … the parameter in GetObject determines here with which model the script should work. 0 is the first model listed in the Application View. 1 is the 
   second model listed in the Application View etc.

2. 2006 is an domain object type. You can find out object types in metamodel of the database you work with. 
   1. Open your model.
   2. Select a package of your database (e.g. Database Oracle package) in Package Explorer (Tools menu).
   3. Right-click the Database Oracle package | Open Metamodel.
   4. Find a class in metamodel (Model menu | Classes). For a domain, it will be class “PERDomain” or "PERDomainOR".
   5. See the Object Type. (Also when double-click the PERDomain/PERDomainOR class to open its properties dialog. On tab General, you can see the Object Type.)
3. GUIDs of data types can be found:
   1. in package DatabaseOracle10g.txg (for Oracle 10g db) 
      or
   2. you can see them in a script for reverse engineering, which is a script "RE Oracle 10g" (for Oracle 10g db). Please see the GetDataType function in the script.

Database: All supported databases. (The script has been written for Oracle database as an example.)
Size: 1 KB
Date: 30 November 2007
Author: Mario

The script will add a new entity to your model.

How to Execute Script:

1. Unzip the attachment.
2. Open it e.g. in Notepad.
3. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window).
4. Run the script. (See the Execute button in the Scripting Window toolbar.)

Notes:

1. var Model = app.Models.GetObject(0); 

   … the parameter in GetObject determines here with which model the script should work. 0 is the first model listed in the Application View. 1 is the 
   second model listed in the Application View etc.

2. 2002 is an entity object type for Oracle db. You can find out object types in metamodel of the database you work with. 

   1. Open your model. 
   2. Select a package of your database (e.g. Database Oracle package) in Package Explorer (Tools menu). 
   3. Right-click the Database Oracle package | Open Metamodel. 
   4. Find a class in metamodel (Model menu | Classes). For an entity, it will be class “PEREntity” or “PEREntityOR”. 
   5. See the Object Type. (Also when double-click the PEREntity/PEREntityOR class to open its properties dialog. On tab General, you can see the Object Type.)

Database: All supported databases. (The script has been written for Oracle database as an example.)
Size: 1 KB
Date: 30 November 2007
Author: Mario

The script will go through all NotNull attributes and will set their notnull constraint name in format NN_nameoftable_number. For names exceeding 30 characters, it will truncate the NN_nameoftable part.

How to Execute Script:

1. Unzip the attachment.
2. Open it e.g. in Notepad.
3. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window).
4. Run the script. (See the Execute button in the Scripting Window toolbar.)

Database: Oracle db 
Size: 2 KB 
Date: 6 December 2007 
Author: Mario

The script will convert all attribute and entity names to lower case. If you need to modify the script, please feel free to do it.

How to Execute Script:

1. Unzip the attachment.
2. Open it e.g. in Notepad.
3. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window).
4. Run the script. (See the Execute button in the Scripting Window toolbar.)

Database: All supported databases 
Size: 1 KB 
Date: 14 December 2007 
Author: Mario

The script will remove content of the Physical Properties tab in Oracle models. If you need to modify the script, please feel free to do it.

How to Execute Script:

1. Unzip the attachment.
2. Open it e.g. in Notepad.
3. Copy and paste the script in the Scripting Window in TDM. (Tools | Scripting Window).
4. Run the script. (See the Execute button in the Scripting Window toolbar.)

Note: Expert mode must be enabled. Click Settings | Options. Select the "General" item and check the Expert Mode check box to enable Expert mode. In standard mode the scripting window is not accessible.

Database: Oracle 10g, Oracle 9i 
Size: 1 KB 
Date: 10 March 2008 
Author: Mario“

The script will rename names of primary keys.

How to Execute Script:

1. Unzip the attachment.
2. Open it e.g. in Notepad.
3. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window).
4. Run the script. (See the Execute button in the Scripting Window toolbar.)

Note:

Expert mode must be enabled. Click Settings | Options. Select the "General" item and check the Expert Mode check box to enable Expert mode. In standard mode, the scripting window is not accessible.

Database: All supported databases 
Size: 1 KB 
Date: 19 March 2008 
Author: Mario

The script will order entites alphabetically before you generate the SQL/DDL script

How to Execute Script:

1. Unzip the attachment.
2. Open it e.g. in Notepad.
3. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window).
4. Run the script. (See the Execute button in the Scripting Window toolbar.)

Note: Expert mode must be enabled. Click Settings | Options. Select the "General" item and check the Expert Mode check box to enable Expert mode. In standard mode, the scripting window is not accessible.

Database: All supported databases 
Size: 1 KB 
Date: 3 April 2008 
Author: Mario

The script creates indexes for all autoincrement columns in MySQL model.

Execute the script in Scripting window (Tools menu, Expert mode on).

Take notice of this part in the script:

var Model = app.Models.GetObject(0); 
//The parameter in GetObject determines here with which model the script should work. 
//0 is the first model listed in the Application View. //1 is the second model listed in the Application View 
//etc.

The script converts caption of entities and attributes to physical name.

Example:

Caption: Order Record Physical Name: T_ORDER_RECORD Result – physical name: Order_Record

How to Execute Script:

1. Unzip the attachment.
2. Open it e.g. in Notepad.
3. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window).
4. Run the script. (See the Execute button in the Scripting Window toolbar.)

Note:

var Model = app.Models.GetObject(0);

//… parameter in GetObject determines with which model the script should work.

//0 = first model listed in the Application View, 1 = second model listed in the Application View etc.

The script moves Notes of entities and attributes to Comments.

All supported databases

How to Execute Script:

1. Unzip the attachment.
2. Open it e.g. in Notepad.
3. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window).
4. Run the script. (See the Execute button in the Scripting Window toolbar.)

Note:

var Model = app.Models.GetObject(0); //… parameter in GetObject determines with which model the script should work. //0 = first model listed in the Application View, 1 = second model listed in the Application View etc.

The script creates as many workspaces as many Schemas are in the model. If entity/view has a selected schema, the particular entity/view will be copied to particular workspace – placement and size of object will remain preserved. For entities, also relationships will be loaded, however, except for those relationships that connect entities of a different schema.

You can use the script to create workspaces by shemas – e.g. after reverse engineering.

How to Execute Script:

1. Unzip the attachment.
2. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). (Expert mode must be turned on.)
3. Run the script. (See the Execute button in the Scripting Window toolbar.)

Note:

var Model = app.Models.GetObject(0); … parameter in GetObject determines with which model the script should work.
0 = first model listed in the Application View,
1 = second model listed in the Application View etc.

Database: All databases with Schemas 
Size: 1 KB 
Date: 13 March 2009 
Author: Mario

The script creates as many workspaces as many Users are in the model. If entity/view/materialized view has a selected user, the particular entity/view/materialized view will be copied to particular workspace – placement and size of object will remain preserved. For entities, also relationships will be loaded, however, except for those relationships that connect entities of a different User.

You can use the script to create workspaces by Users in Oracle models – e.g. after reverse engineering.

How to Execute Script:

1. Unzip the attachment.
2. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). (Expert mode must be turned on.)
3. Run the script. (See the Execute button in the Scripting Window toolbar.)

Note:

var Model = app.Models.GetObject(0); … parameter in GetObject determines with which model the script should work.
0 = first model listed in the Application View,
1 = second model listed in the Application View etc.

Database: Oracle db 
Size: 1 KB 
Date: 17 March 2009 
Author: Mario

The script goes through all attributes and changes every Char data type (without specification if the length is in BYTE or CHAR) to Varchar2. 
Example: Char(20 BYTE) will be changed to Varchar2(20).

You can use the script to change a data type.

How to Execute Script:

1. Unzip the attachment.
2. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). (Expert mode must be turned on.)
3. Run the script. (See the Execute button in the Scripting Window toolbar.)

Note: var Model = app.Models.GetObject(0); … parameter in GetObject determines with which model the script should work.

0 = first model listed in the Application View, 
1 = second model listed in the Application View etc.

Database: Oracle db 
Size: 1 KB 
Date: 17 March 2009 
Author: Mario

The script goes through all identifying relationships in your model and clears the Generate Index to Foreign Keys checkbox.

When you create a relationship in Toad Data Modeler, the Generate Index to Foreign Keys checkbox in the Relationship Properties dialog is selected by default. Use this script if you need to clear this checkbox for all identifying relationships in your model.

How to Execute Script:

1. Unzip the attachment.
2. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). (Expert mode must be turned on.)
3. Run the script. (See the Execute button in the Scripting Window toolbar.)

Note: var Model = app.Models.GetObject(0); … parameter in GetObject determines with which model the script should work.
0 = first model listed in the Application View, 
1 = second model listed in the Application View etc.

Database: Oracle db (9i, 10g, 11g) 
Size: 1 KB 
Date: 14 May 2009 
Author: Mario

The script goes through all identifying relationships in your model where it clears the Generate Index to Foreign Keys checkbox, and through all non-identifying relationships where it selects the Generate Index to Foreign Keys checkbox.

When you create a relationship in Toad Data Modeler, the Generate Index to Foreign Keys checkbox in the Relationship Properties dialog is selected by default. Use this script if you need to:

• clear this checkbox for all identifying relationships and at the same time
• select this checkbox for all non-identifying relationships in your model.

How to Execute Script:

1. Unzip the attachment.
2. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). (Expert mode must be turned on.)
3. Run the script. (See the Execute button in the Scripting Window toolbar.)

Note: var Model = app.Models.GetObject(0); … parameter in GetObject determines with which model the script should work.

0 = first model listed in the Application View,
1 = second model listed in the Application View etc.

Database: Oracle db (9i, 10g, 11g) 
Size: 1 KB 
Date: 14 May 2009 
Author: Mario

When you convert a model from Oracle to MS SQL Server, user/schema values assigned to objects in Oracle model will not be converted to MS SQL Server schemas automatically. This script allows you to load settings from Oracle model and set appropriate settings in your newly created MS SQL Server model.

How to Execute Script:

1. Unzip the attachment.
2. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). (Expert mode must be turned on.)
3. Run the script. (See the Execute button in the Scripting Window toolbar.)
   Important: Both models must be open in Toad Data Modeler and the following variables must be set properly in the script:
   var OldModel = app.Models.GetObject(0); // Oracle model [old] var NewModel = app.Models.GetObject(1); // SQL Server model [new]
   … parameter in GetObject determines with which model the script should work. 0 = first model listed in the Application View, 1 = second model listed in the Application View etc.

Comments from Primary Keys get migrated to Foreign Keys.

If you need Comment fields content to migrate from parent keys to foreign keys attributes in child entities, execute the script on the model.

How to Execute Script:

1. Unzip the attached CommentsMigratePKtoFK.zip.
2. Copy and paste the script in the Scripting Window in Toad Data Modeler. (Tools | Scripting Window). (Expert mode must be turned on.)
3. Run the script. (See the Execute button in the Scripting Window toolbar.)

Database: database independent 
Size: 1 KB 
Date: November 3, 2011 
Author: Mario

migrate comments in toad data modeler.pdf