Scripts

Change the Referenced Object Owner of Synonyms

ricktg — Tue, 30 Sep 2008 13:36:14 GMT

This script changes the Referenced Owner/Schema of Synonyms. This Schema does not need to be created on MR.

To change this, you only need to specify which owner you want to change and what is the new owner name. Just look at the code and you'll see where to input that.

Here's an example:

I have this Synonym: create or replace synonym Schema001.SYN_TBL_TEST for Schema002.TBL_TEST;

Now, i want to change the Schema002 name. So i change the following lines:

fromUser = "Schema002"
toUser = "NewSchema003"

Then i run it. Done! The synonym will be like this:

create or replace synonym Schema001.SYN_TBL_TEST for NewSchema003.TBL_TEST;

Att,

Ricardo

How to export ModelRight info to Excel

admin — Tue, 17 Jun 2008 13:42:26 GMT

Here's a simple example of how to export ModelRight Model info to Excel. Add a user-defined script object and copy/paste the following into it. Then run it:

Sub Evaluate_OnLoad()
Set Context = CreateObject("SCF.ScriptContext")
Set ThisScript = Context.Object
Set Options = Context.Options
Set Model = ThisScript.Model

Dim NumRows, x1, col
NumRows = 2
Set ExcelDoc = CreateObject("Excel.Application")
ExcelDoc.Visible = true
ExcelDoc.Workbooks.Add
ExcelDoc.Range("A1").Value = "Table"
ExcelDoc.Range("B1").Value = "Column"
ExcelDoc.Range("C1").Value = "Datatype"
Set Tables = Model.AsObject.Children("Table")
For Each Table In Tables
  TableName = Table.Property("Name").AsString
  Set Columns = Table.Children("Column")
  For Each Column in Columns
   ExcelDoc.Range("A" + Cstr(NumRows)).Value = TableName
   ExcelDoc.Range("B" + Cstr(NumRows)).Value = Column.Property("Name").AsString
   ExcelDoc.Range("C" + Cstr(NumRows)).Value = Column.Property("Datatype").AsString
   NumRows = NumRows + 1
  Next
Next

ExcelDoc.Columns("A:C").EntireColumn.AutoFit
ExcelDoc.Rows("1:" + cstr(NumRows)).EntireRow.AutoFit

End Sub

GenerateIndexes

modelright — Tue, 06 May 2008 20:38:41 GMT

This is a script that generates the SQL DDL for all indexes. It is useful if one wants to generate the SQL of all indexes separated from the SQL of tables.

GenerateConstraints

modelright — Tue, 06 May 2008 20:38:09 GMT

This is a script that generates the SQL DDL for all constraints (Primary, Unique and Foreign Keys). It is useful if one wants to generate the SQL of all key constraints separated from the SQL of tables.

create a synonym in a "To Schema" for each Table in the "From Schema"

admin — Mon, 31 Mar 2008 21:03:13 GMT

This script will create a synonym in a "To Schema" for each Table in the "From Schema" (if it doesnt already exist). You should edit the FromSchemaName and ToSchemaName before running the script.

To run it:

1) bring up the Script Explorer

2) right click on "User Defined" and select "New Script"

3) copy/paste the attachment into the edit control

4) hit the run button (the first icon in the toolbar above the edit control)

Note: you can undo/redo the effects of this script as with any Model changes

Creates FK indexes and deletes redundant indexes

admin — Fri, 14 Mar 2008 04:07:08 GMT

This script is for Oracle, but could easily be adapted for other databases.

It iterates over all tables and creates an FK index if one doesnt already exist and if it wouldnt be redundant.

It also deletes any indexes that are redundant - ie. covered by another index.

It has a couple variables at the beginning of the Evaluate_OnLoad sub that you can set:

DeleteIfPrefixMatches - set to true if you want the index deleted if it matches the prefix of another index. Otherwise, it needs to match the entire index.

WarnOnly - dont actually delete or create any indexes, just output what it would do. Of course, even if you do delete indexes, you can always undo the delete.

creates join indexes for all relations

admin — Thu, 14 Feb 2008 14:48:00 GMT

This script will iterate over all the tables in your model and all of their relations and create an FK/join index for the relation if one doesnt already exist.

Only makes sense for databases like Oracle that don't auto create FK/Join indexes. For databases that do, ModelRight auto-creates them as well.

To run it:

1) bring up the Script Explorer

2) right click on "User Defined" and select "New Script"

3) copy/paste the attachment into the edit control

4) hit the run button (the first icon in the toolbar above the edit control)

Note: you can undo/redo the effects of this script as with any Model changes

Copy Table positions from one Diagram to another

admin — Fri, 11 Jan 2008 14:28:44 GMT

This script will copy Table and View positions from one Diagram to another. You can specify a From and To Diagram that you want to use:

To run it:

1) bring up the Script Explorer

2) right click on "User Defined" and select "New Script"

3) copy/paste the attachment into the edit control

4) change the FromModelSubsetName, FromDiagramName, ToModelSubsetName, and ToDiagramName in the Evaluate_OnLoad subroutine to match the names of the Model Subsets and Diagrams that you want to use.

5) hit the run button (the first icon in the toolbar above the edit control)

Note: you can undo/redo the effects of this script as with any Model changes

Rename Contraint Names

admin — Wed, 17 Oct 2007 23:57:55 GMT

Similar to Update Constraint Names, but this changes key constraint, fk constraint, check constraint, lob storage names regardless of whether they are still using their default names. It tries not to rename them subsequently though.

Auto Rename Migrating Column to Prefix with Parent Table Name

admin — Thu, 11 Oct 2007 19:58:57 GMT

As of Build 2020, ModelRight has the ability to rename a migrating column as it migrates.

For example, you could prefix a migrating column with the parent table name. We did this via a script that gets evaluated and returns a name for the migrating column. This approach is more general than just being able to prefix with something specific - like the parent table name.

To use it go to the Script Explorer (probably a tab for it next to Model Explorer), expand to <database>/Relation/Auto Rename Migrating Column. Enter the following text for the script in the property browser (this will prefix the migrating column with the parent table name):

' Auto Rename Migrating Column Script
Sub Evaluate_OnLoad()
    Dim Context
    Set Context = CreateObject("SCF.ScriptContext")
    Dim Document
    Set Document = Context.ScriptDocument
    Dim MigratingColumn
    Set MigratingColumn = Context.MigratingColumn
    Dim MigratingRelation
    Set MigratingRelation = Context.MigratingRelation
    Dim ParentTable
    Set ParentTable = MigratingRelation.Property("Parent Table").AsObject
    Document.Write(ParentTable.Property("Name").AsString & "_" & MigratingColumn.Property("Name").AsString)
End Sub

If you want all of your models to use this, then right click on the script in the script explorer and select Save to Disk.

Update Constraint Names Script

admin — Fri, 16 Mar 2007 18:23:14 GMT

This script will go through all the tables in the model and update key constraints and relations that are still using a default name. Place the script in a sub-folder to the MR install/exe called Scripts\User Defined and it will appear in the Scripts Explorer tab under User Defined scripts. Select the script in the explorer and then click the run button in the Property Browser.