How to Access Entity Tablespace

Toad Data Modeler
1

Hi,
How can I access Entity, Index and Key tablespace attributes in a script? I can’t seem to find them in the Reference.
I want to write a script to loop all entities and check the Tablespace is set to the correct values.
Thanks
Malcolm

2

As far as I see, TDM’s attributes have some generic names, for example Entities have “Comments” property, even in MSSQL where MS named it “Description”. So in order to change Description you have to change Comments property from a script.
So maybe the tablespace property has other name, maybe “Schema”?

3

Hi Malcolm,

please see PEREntityOR and PERTablespaceOR classes.

Here you are an example:

function main(){

// itearate entities
Log.Information("*** E N T I T I E S *** ");
for (i=0; i<Model.Entities.Count; i++)
{
Ent = Model.Entities.GetObject(i);
Log.Information("Entity: " +Ent.Name);
if(Ent.Tablespace != null)
{
Log.Information(" --- Entity '" +Ent.Name+ "' has tablespace '"+Ent.Tablespace.Name+"'");
}
}

Log.Information("");

// iterate tablespaces
Log.Information("*** T A B L E S P A C E S *** ");
for (i=0; i<Model.Tablespaces.Count; i++)
{
TS = Model.Tablespaces.GetObject(i);
Log.Information("Tablespace: "+TS.Name);
for (j = 0; j<TS.Entities.Count; j++)
{
EntityWithTS = TS.Entities.GetObject(j)
Log.Information(" --- Tablespace '"+TS.Name+"' is assigned to entity '"+EntityWithTS.Name+"'");
}

}
}

When you iterate entities, you can access tablespace via Ent.Tablespace.Name. You can also iterate tablespaces and access entity name as it is mentioned in the second example.

See the screen shot, please.

Regards,

Vaclav

tablespaces.png
tablespaces.png938×876 38.5 KB

4

Thanks Vaclav,

That was helpful. I’ve figured out how to access the Key tablespace.

Another question if I may:
I’d like to extend my script to assign tablespace to a key;

if ( key.Tablespace.Name is not set )
set tablespace to required value (eg that of the table)
else if ( key.Tablespace.Name is the wrong name )
set tablespace to required value (eg that of the table)

How do I create/replace a tablespace for a Key?

Thanks
Malcolm

5

Hi Malcolm,

here is the script. Don’t forget to select your model in and rename it to Model in the “Name in script” field. See previous screen shot.

function main(){
// itearate entities
Log.Information("*** E N T I T I E S *** ");
for (i=0; i<Model.Entities.Count; i++)
{
Ent = Model.Entities.GetObject(i);
Log.Information("Entity: " +Ent.Name);
if(Ent.Tablespace != null)
{
// create new EntityTableSpaceName variable and put Entity tablespace name into it
EntityTableSpaceName = Ent.Tablespace.Name;

         Log.Information(" --- Entity '" +Ent.Name+ "' has tablespace '"+Ent.Tablespace.Name+"'");
        
         // iterate keys
         for(j =0; j<Ent.Keys.Count; j++)
               {
                   Key = Ent.Keys.GetObject(j); 
                  
                   if(Key.Tablespace == null)
                       {
                            Key.SetLinkedObject("Tablespace", Ent.Tablespace);  
                            // Note: we cannot use simple 'Key.Tablespace = Ent.Tablespace', because if you
                            // deleted the tablespace, it wouldn't be automatically deleted from the Entity.
                            // That's why it is necessary to use the SetLinkedObject procedure.
                       }
                  
                   else
                       {         
                       // create new KeyTableSpaceName variable and put Entity tablespace name into it
                       KeyTableSpaceName = Key.Tablespace.Name;
                       Log.Information(" ----- Key '" +Key.Name+ "' has tablespace '"+Key.Tablespace.Name+"'");
                      
                       // compare values
                       if(KeyTableSpaceName != EntityTableSpaceName)
                           { 
                           // replace tablespace
                           Key.SetLinkedObject("Tablespace", Ent.Tablespace);
                           Log.Information(" ----- Table space of key '" +Key.Name+ "' has been changed to '"+Key.Tablespace.Name+"'");
                           }
                       }
                  
               }

}
}

}

Regards,

Vaclav