Is there a macro to create synonyms for selected tables?

I am looking for a macro that creates synonyms for the selected tables. (Oracle and SQL Server)

  • Hi,

    Here's a macro that should work for both Oracle and SQL Server.  It's very basic so you may need to add things to fit your needs but should get you started:

    2045.Create Oracle-SQL Server Synonyms.txt
    'TITLE:  Create Oracle/SQL Server Synonyms
    
    'ER/Studio Variables
    Dim diag As Diagram
    Dim mdl As Model
    Dim submdl As SubModel
    
    Dim ent As Entity
    
    Sub Main
    Debug.Clear
    	Set diag = DiagramManager.ActiveDiagram
    	Set mdl = diag.ActiveModel
    	Set submdl = mdl.ActiveSubModel
    
    	Dim so As SelectedObject
    	Dim OracleSyn As OracleSynonym
    	Dim SQLSyn As SQLServerSynonym
    	Dim dbPlatform As String
    
    	dbPlatform = "NONE"
    
    	If mdl.Logical Then
    		MsgBox "You must be on the physical model"
    		Exit All
    	End If
    
    	Debug.Print mdl.DatabasePlatform
    
    	If submdl.SelectedObjects.Count = 0 Then
    		MsgBox "No objects selected"
    		Exit All
    	End If
    
    	If InStr(mdl.DatabasePlatform , "Oracle") <> 0 Then
    		dbPlatform = "ORACLE"
    	End If
    
    	If InStr(mdl.DatabasePlatform , "SQL Server") <> 0 Then
    		dbPlatform = "SQLSERVER"
    	End If
    
    	If dbPlatform = "NONE" Then
    		MsgBox "Database platform must be SQL Server or Oracle"
    		Exit All
    	ElseIf dbPlatform = "ORACLE" Then
    	For Each so In submdl.SelectedObjects
    		If so.Type = 1 Then
    			Set ent = mdl.Entities.Item(so.ID)
    			mdl.OracleSynonyms.Add(ent.TableName, ent.Owner, ent.TableName, ent.Owner)
    		End If
    	Next
    
    	ElseIf dbPlatform = "SQLSERVER" Then
    	For Each so In submdl.SelectedObjects
    		If so.Type = 1 Then
    			Set ent = mdl.Entities.Item(so.ID)
    			mdl.SQLServerSynonyms.Add(ent.TableName, ent.Owner, ent.TableName, ent.Owner)
    		End If
    	Next
    
    
    	End If
    
    
    End Sub