Toad World® Forums

Toad Data Modeler Scripts


#1
Script Description Override Identity 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: Unzip the attachment. Copy and paste the script in the Scripting Window in Toad Data Modeler. 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 2000Size: 1 KBDate: 16 November 2007Author: Mario Add Prefix to Objects 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: Unzip the attachment. Open it e.g. in Notepad. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). 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 KBDate: 20 November 2007Author: Mario Add Domain Object The script will add a new domain to your model. How to Execute Script: Unzip the attachment. Open it e.g. in Notepad. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). Run the script. (See the Execute button in the Scripting Window toolbar.) Notes: 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. 2006 is an domain object type. You can find out object types in metamodel of the database you work with. Open your model. Select a package of your database (e.g. Database Oracle package) in Package Explorer (Tools menu). Right-click the Database Oracle package | Open Metamodel. Find a class in metamodel (Model menu | Classes). For a domain, it will be class “PERDomain” or "PERDomainOR". 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.) GUIDs of data types can be found: in package DatabaseOracle10g.txg (for Oracle 10g db) or 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 KBDate: 30 November 2007Author: Mario Add Entity Object The script will add a new entity to your model.How to Execute Script: Unzip the attachment. Open it e.g. in Notepad. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). Run the script. (See the Execute button in the Scripting Window toolbar.) Notes: 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. 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 KBDate: 30 November 2007Author: Mario Rename NN (Not Null) constraint Names 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: Unzip the attachment. Open it e.g. in Notepad. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). Run the script. (See the Execute button in the Scripting Window toolbar.) Database: Oracle db Size: 2 KB Date: 6 December 2007 Author: Mario Convert Entity, Attribute Names to Lower Case 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: Unzip the attachment. Open it e.g. in Notepad. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). 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 Remove Physical Properties 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: Unzip the attachment. Open it e.g. in Notepad. Copy and paste the script in the Scripting Window in TDM. (Tools | Scripting Window). 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`` Rename Primary Key Names The script will rename names of primary keys. How to Execute Script: Unzip the attachment. Open it e.g. in Notepad. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). 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 Order Entries Alphabetically The script will order entites alphabetically before you generate the SQL/DDL script How to Execute Script: Unzip the attachment. Open it e.g. in Notepad. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). 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 Create Indexes for Auto-increment Columns in MySQL 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. Convert Captions to Physical Name 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: Unzip the attachment. Open it e.g. in Notepad. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). 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. Notes to Comments Conversion The script moves Notes of entities and attributes to Comments. All supported databases How to Execute Script: Unzip the attachment. Open it e.g. in Notepad. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). 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. Create Workspaces by Schemas 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: Unzip the attachment. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). (Expert mode must be turned on.) 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 Create Workspaces by Users 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: Unzip the attachment. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). (Expert mode must be turned on.) 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 Change Data Type 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: Unzip the attachment. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). (Expert mode must be turned on.) 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 Uncheck 'Generate Index to Foreign Keys' for Identifying Relationships 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: Unzip the attachment. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). (Expert mode must be turned on.) 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 Check/Uncheck the 'Generate Index to Foreign Keys' checkbox 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: Unzip the attachment. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). (Expert mode must be turned on.) 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 Set Schemas according to Oracle Users 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: Unzip the attachment. Copy and paste the script in the Scripting Window in TDM3. (Tools | Scripting Window). (Expert mode must be turned on.) 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. Migrate comment fields to FKs 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: Unzip the attached CommentsMigratePKtoFK.zip. Copy and paste the script in the Scripting Window in Toad Data Modeler. (Tools | Scripting Window). (Expert mode must be turned on.) 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

This is a companion discussion topic for the original entry at https://blog.toadworld.com/toad-data-modeler-scripts