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


/********** start of script *************/

' Iterates over all the tables in the model and all of their relations
' and creates an FK/join index if one doesnt already exist

Sub CreateFKIndexes(Table, Document, Framework)
	Dim Relations
	Dim Relation
	Dim Index
	Set Relations = Table.Children("Relation")
	Num = 0
	For Each Relation in Relations
		If Not Relation.HasProperty("Index") Then
			Document.Write("Creating FK Index for Relation " & Table.Property("Name").AsString & "." & Relation.Property("Name").AsString & vbLf)
			Set Index = Framework.CreateObject("Index", Table)
			Dim RefProp
			Set RefProp = Framework.CreatePropertyValue("Relation", "Index")
			RefProp.FromObject(Index)
	        Relation.SetProperty "Index", RefProp
			Dim BoolProp
			Set BoolProp = Framework.CreatePropertyValue("Index", "Auto Generated By Key")
			BoolProp.FromBoolean(True)
	        Index.SetProperty "Auto Generated By Key", BoolProp
	        Index.DeleteProperty("Name")
		End If
	Next
End Sub

Sub Evaluate_OnLoad()
	Set Context = CreateObject("SCF.ScriptContext")
	Set Document = Context.ScriptDocument
	Set ThisScript = Context.Object
	Set Options = Context.Options
	Set Model = ThisScript.Model
	Set Framework = CreateObject("SCF.ScriptFramework")
	Document.Write("Creating Model " & Model.AsObject.Property("Name").AsString & " FK Indexes..." & vbLf)
	Model.BeginTransaction("Create FK Indexes")
	Set Tables = Model.AsObject.Children("Table")
	For Each Table In Tables
		Call CreateFKIndexes(Table, Document, Framework)
	Next
	Model.EndTransaction()
	Document.Write("Updating Model " & Model.AsObject.Property("Name").AsString & " Done!" & vbLf)
End Sub