New to Toad Data Modeler 5.2 - How to change all Primary Key attributes Datatypes and Identity flags

Toad Data Modeler
1

I have inherited an old data model from Case Studio (Microsoft SQL Server 2005) which when loaded into Toad Data Modeler it isn’t properly generating the attributes.

The current attributes are defined as Domain N12 (which is a simple Numeric(12,0). This itself is fine, but it is missing the fact all these attributes should also be Identity columns.

If I manually change the Primary Key attribute from N12 to Numeric(12,0), the Identity property fills in.

I have over 500 entities and manually doing this is very painful.

Is there a macro or something I’m missing which could help me reset all the primary key attributes to use the NUMERIC(12,0) datatype and have an Identity flag set (Seed = 1, Increment = 1)?

2

Hi,

what version of Toad Data Modeler do you have, please?

In version 5.2 (in SQL Server models) when you draw relationship between entities where in PK attribute a domain with identity is used, the identity in FK attribute in child entity is automatically overwritten.

Regards,

Vaclav

3

BTW: here you can find article that shows how to change domain for multiple columns at once: www.toadworld.com/…/assign-existing-domain-to-selected-columns.aspx

4

I am currently running TDM 5.2.4.25

I think I found a solution:

function main(){

var e, a

var DataType_numeric = Model.ModelDef.DataTypes.GetObjectByName(“Numeric(%p1,%p2)”);

// Go Through all Entities

for(e=0;e<Model.Entities.Count; e++)

{

Entity = Model.Entities.GetObject(e);

Log.Information("Entity: "+Entity.Name);

// Go Through all the Attributes for the Entity

for(a=0;a<Entity.Attributes.Count; a++)

{

Attr = Entity.Attributes.GetObject(a);

//Log.Information("Attribute: "+Attr.Name);

// Find Primary Key(s)

if (Attr.IsPrimaryKey == true)

{

Log.Information("Attribute [Primary Key]: "+ Attr.Name);

// Assuming we found the Primary Key, if the datatype is a NUMERIC set it to be NUMERIC(12,0) that is an Identity and has Seed and Increment of 1

Attr.SetLinkedObject(“DataType”, DataType_numeric) ;

Attr.DataTypeParam1 = ‘12’;

Attr.DataTypeParam2 = ‘0’;

Attr.Identity = true;

Attr.IdentityIncrement = ‘1’;

Attr.IdentitySeed = ‘1’;

}

}

}

}

It takes a long time to iterate through these elements though. Any thoughts on how to improve overall performance of TDM?

5

For some reason restarting really sped this process up. Looks like this did the trick. Thanks for the tip Vaclav.

6

Great, I am glad you found a way to achieve desired results. And the script is OK. There seems to be no better way to write it.

Vaclav & TDM team