Script to Remove Comments after Conversion
Hi,
As you probably know, Toad Data Modeler allows you to convert a model from one database system to another. You can reverse engineer a physically existing Oracle database structure and convert your model to PostgreSQL or MS SQL Server model etc. Tables, relationships, columns and data types will be converted automatically together with logical information. Other database specific items like Triggers, Stored Procedures, Views etc. can be converted too, however, if you select such object types they will be automatically commented out in your newly created model. Why? Because you can write SQL code for Views or Triggers etc. that will work in Oracle, but won’t work in MS SQL Server. And you can face similar problems after converting Oracle 9 model to Oracle 11 model.
How can we easily remove comments (I mean automatically added comments) from the newly created model? - Via scripting!
See below a script that removes comments from newly created Oracle 11g model (converted from Oracle 10g model.)
function main()
{
var app = System.GetInterface('Application');
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.
var i, j, k;
var Entity, Trigger, View, Procedure, Function, CheckConstraint, Attribute, Domain, Default;
Model.Lock();
//Triggers, CheckConstraints
for (i=0; i<Model.Entities.Count; i++)
{
Entity = Model.Entities.GetObject(i);
for (j=0; j<Entity.Triggers.Count; j++)
{
Trigger = Entity.Triggers.GetObject(j);
Trigger.SQL = RemoveCommentChars(Trigger.SQL);
}
for (j=0; j<Entity.CheckConstraints.Count; j++)
{
CheckConstraint = Entity.CheckConstraints.GetObject(j);
CheckConstraint.SQL = RemoveCommentChars(CheckConstraint.SQL);
}
for (j=0; j<Entity.Attributes.Count; j++)
{
Attribute = Entity.Attributes.GetObject(j);
for (k=0; k<Attribute.CheckConstraints.Count; k++)
{
CheckConstraint = Attribute.CheckConstraints.GetObject(k);
CheckConstraint.SQL = RemoveCommentChars(CheckConstraint.SQL);
}
}
}
//Domain CheckConstraints
for (i=0; i<Model.Domains.Count; i++)
{
Domain = Model.Domains.GetObject(i);
for (j=0; j<Domain.CheckConstraints.Count; j++)
{
CheckConstraint = Domain.CheckConstraints.GetObject(j);
CheckConstraint.SQL = RemoveCommentChars(CheckConstraint.SQL);
}
}
//Defaults
for (i=0; i<Model.Defaults.Count; i++)
{
Default = Model.Defaults.GetObject(i);
Default.Value = RemoveCommentChars(Default.Value);
}
//Views
for (i=0; i<Model.Views.Count; i++)
{
View = Model.Views.GetObject(i);
View.SQL = RemoveCommentChars(View.SQL);
}
//Procedures
for (i=0; i<Model.Procedures.Count; i++)
{
Procedure = Model.Procedures.GetObject(i);
Procedure.SQL = RemoveCommentChars(Procedure.SQL);
}
//Functions
for (i=0; i<Model.Functions.Count; i++)
{
Function = Model.Functions.GetObject(i);
Function.SQL = RemoveCommentChars(Function.SQL);
}
Model.UnLock();
}
function RemoveCommentChars( strValue )
{
return strValue.replace(/^--/g,"").replace(/\n--/g,"\n");
}
How to run the script in Toad Data Modeler? Do the following, please:
- Enable Expert Mode - click Settings | Options and in section General, enable the Expert Mode checkbox.
- Click Tools | Scripting Window to open a form from where the script will be executed.
- Copy and paste the script to the main area.
- Click the Execute button (green arrow icon).
Regards,
Vaclav & TDM team