# Massively updating the attributes data type to chosen domain

**URL:** <https://forums.toadworld.com/t/massively-updating-the-attributes-data-type-to-chosen-domain/32405>\
**Category:** Toad Data Modeler\
**Created:** [January 26, 2015, 1:32am UTC](https://forums.toadworld.com/t/massively-updating-the-attributes-data-type-to-chosen-domain/32405 "2015-01-26T01:32:02Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![clement.bitton](https://avatars.discourse-cdn.com/v4/letter/c/ce7236/32.png) [@clement.bitton](https://forums.toadworld.com/u/clement.bitton)\
**Post date:** [January 26, 2015, 1:32am UTC](https://forums.toadworld.com/t/massively-updating-the-attributes-data-type-to-chosen-domain/32405/1 "2015-01-26T01:32:02Z")

</div>

Hi all,

I have an issue with Toad Data Modeler.

We are modeling a normalized layer for our data warehouse.

For homogeneity reason we wish to make sure that each field use a standard Domain, defined in TOAD.

But changing the attributes one by one take forever and I am looking for a way to massively change them, I tried to use the Export/import excel function but it doesn’t work. My understanding is that this functionality is not provided by this import/export.

Is there any way you can recommend me to do so ?

Or there are no way to your knowledge to do such operation ?

Thank you in advance for your help !

---

<div class="post-metadata">

**Author:** ![Lukas.Knapek](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/lukas.knapek/32/269_2.png) [@Lukas.Knapek](https://forums.toadworld.com/u/Lukas.Knapek)\
**Post date:** [January 26, 2015, 7:40am UTC](https://forums.toadworld.com/t/massively-updating-the-attributes-data-type-to-chosen-domain/32405/2 "2015-01-26T07:40:56Z")

</div>

Hello Clement,

As you guessed, Excel Import/Export isn’t really meant for this purpose. In your case you might want to use scripting to change multiple attribute data types. Enable **Expert**  **Mode** in **Options | General** and go to **Expert Mode Menu | Scripting Window**. This is the place where you write your own scripts in TDM.

There are some resources available on scripting, see **Help | Projects and Models | Scripting and Customization**. If you like, you may try the following script, which changes **ALL** attributes to “ **Domain1** ” including PKs, FKs (you can change the name in script).

function main()  
{  
var e, a, Entity, Attribute;  
var Domain = Model.Domains.GetObjectByName(‘Domain1’); //Change name of the Domain here

```
if (Domain == null)
{
   Log.Information('Domain "Domain1" does not exist.')
   return;
}

```

Model.Lock();

for (e=0; e\<Model.Entities.Count; e++)  
{  
Entity = Model.Entities.GetObject(e);  
Entity.Lock();

```
  for (a=0; a<Entity.Attributes.Count; a++)
  {
      Attribute = Entity.Attributes.GetObject(a);
      if (Attribute.DataType!=null)
      {
          Attribute.SetLinkedObject('DataType', null);
          Attribute.ChangeDataType(Domain);
      }
  }
  Entity.UnLock();

```

}  
Model.UnLock();  
}

Regards,

Lukas

---

<div class="post-metadata">

**Author:** ![Vaclav\_Frolik](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/vaclav_frolik/32/12_2.png) [@Vaclav\_Frolik](https://forums.toadworld.com/u/Vaclav_Frolik)\
**Post date:** [January 26, 2015, 9:16am UTC](https://forums.toadworld.com/t/massively-updating-the-attributes-data-type-to-chosen-domain/32405/3 "2015-01-26T09:16:02Z")

</div>

Hi, you might find some useful information in this blog article:

[www.toadworld.com/…/assign-existing-domain-to-selected-columns.aspx](http://www.toadworld.com/products/toad-data-modeler/b/weblog/archive/2013/02/25/assign-existing-domain-to-selected-columns.aspx)

It shows how to assign Domain to selected attributes.

---

<div class="post-metadata">

**Author:** ![clement.bitton](https://avatars.discourse-cdn.com/v4/letter/c/ce7236/32.png) [@clement.bitton](https://forums.toadworld.com/u/clement.bitton)\
**Post date:** [January 27, 2015, 5:54pm UTC](https://forums.toadworld.com/t/massively-updating-the-attributes-data-type-to-chosen-domain/32405/4 "2015-01-27T17:54:54Z")

</div>

Hi

Thanks for you answers, both of you.

Vaclav, this package is useful but not precisely in that case, since I wish to apply the domain to certain type of fields, depending on their data type, and the object explorer doesn’t show data type.

Lukas, I reused your script, and after few tweak I made it work.

Here is the script I used : (WARNING IT GENERATE LOTS OF LOG, i still need to clean this)

* * *

function main()

{

var e, a, Entity, Attribute, aqString, aSubString, Res;

// var Domain = Model.Domains.GetObjectByName(‘Domain1’); //Change name of the Domain here

/\* if (Domain == null)

{

Log.Information(‘Domain ‘+Domain+’ does not exist.’)

return;

}\*/

Model.Lock();

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

{

Entity = Model.Entities.GetObject(e);

Entity.Lock();

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

{

Log.Information('Check the table '+Entity.Name);

Attribute = Entity.Attributes.GetObject(a);

if (Attribute.DataType!=null)

{

var data\_type = Attribute.DataType.Name;

var attribute\_name = Attribute.Name;

Log.Information('Check the ‘+attribute\_name+’ field of type '+data\_type);

var not\_sys\_table = 0

var Domain = Model.Domains.GetObjectByName(‘Enculer’);

switch (attribute\_name) {

case ‘sys\_sourcename’ :

var Domain = Model.Domains.GetObjectByName(‘sys\_large’);

var text = ‘sys\_large’;

break;

default :

var text\_att = attribute\_name;

var not\_sys\_table = 1;

Log.Information(‘Have not found ‘+text\_att +’ in the system attributes’ ) ;

}

if(not\_sys\_table == 1)

{

Log.Information('We check the other type of fields ’ +data\_type ) ;

switch(data\_type) {

case ‘Character varying’:

if(Attribute.DataTypeParam1 \<180){

var Domain = Model.Domains.GetObjectByName(‘small text’);

var text = ‘small\_text’;

}

else if(Attribute.DataTypeParam1 \>1800){

var Domain = Model.Domains.GetObjectByName(‘large text’);

var text = ‘large\_text’; }

else {

var Domain = Model.Domains.GetObjectByName(‘medium text’);

var text = ‘medium\_text’;

}

break;

case ‘Character varying(%p1)’:

if(Attribute.DataTypeParam1 \<180){

var Domain = Model.Domains.GetObjectByName(‘small text’);

var text = ‘small\_text’;

}

else if(Attribute.DataTypeParam1 \>1800){

var Domain = Model.Domains.GetObjectByName(‘large text’);

var text = ‘large\_text’; }

else {

var Domain = Model.Domains.GetObjectByName(‘medium text’);

var text = ‘medium\_text’;

}

break;

case ‘Timestamp without time zone’:

var Domain = Model.Domains.GetObjectByName(‘date and time’);

var text = ‘date and time’;

break;

default :

var Domain = Model.Domains.GetObjectByName(‘none existing domain’);

var text\_att = Attribute.Name;

var not\_sys\_table = 1

Log.Information('Have not found in other '+text\_att+ 'with type '+Attribute.DataType ) ;

}

}

if (Domain == null)

{

Log.Information(‘Domain ‘+text+’ does not exist.’)

return;

}

Attribute.SetLinkedObject(‘DataType’, null);

Attribute.ChangeDataType(Domain);

}

}

Entity.UnLock();

}

Model.UnLock();

}

* * *

It take a little bit of time, and you may wish to change the rules to apply the different domain but it works.

One issue, if there is a attribute he can’t change, the function will stop and will leave your model and entities locked, you may need to use a othe piece of code to unlock them. (since I don’t know how to do it manually)
