Toad data modeler version 5.5
Database: MS Sql Server 2014
I am trying to auto generate indexes for PK and also name indexes based on their properties(clustered, non clustered, columnstore etc). I would like to prefix the index based on the index type
Is there an option to do that in Toad data modeler to auto generate based on type of index or at least auto generate index for primary key similar to what we have for FK index generation.
Hello,
for this operation is the best way to create some macro or script. Macros are very powerfull tools in TDM. TDM doesn’t generate indexes for PK only for foreign key, but I think SQL Server generate index for each PK automatically.
Daril
Yes Sql Server generate index automatically. But we have some naming conventions to follow for other indexes that we create based on index properties.
I am new to TDM scripting and I found a sample/working script to create macro for renaming indexes. But I am not able to modify that to apply for creating new indexes.
Is there any existing sample script for this type in Toad or a Macro that can help me?
At first you need to enabled Expert Mode in TDM. Is is in Main Menu - Settings - Options - General - Expert Mode. Now you can open Scripting Window in which you are able create small scripts and quickly try if it is OK (code for macro are very similar).
In scripting Window choose one of opened model and by arrow button shift it to right (this model will be available for script). Bellow is Example script:
function main(){
var Entity = Model.Entities.GetObject(0); //Get First Entity from model
var Index = Entity.CreateNewObject(2012); //2012 is Object Type for Index, you can find more object types in Reference Guide
Index.Name = “AAA”;
var IndexItem = Index.CreateNewObject(2013); //2013 is Object Type for Index Item
var Attr = Entity.Attributes.GetObject(0); //Get First Attribute of Entity
IndexItem.Attribute = Attr;
Attr.LinkObject(IndexItem); //Create Back Link. Attribute must know in which indexes it is
Model.RefreshModel(); //Refreshed information in GUI
}
A lot of information is in Reference Guide. There are list of properties of each object. Objects for Physical Model has Prefix PER. Object of MS SQL Database has postfix MS.
Daril