在Powerdesigner或者ER/Studio中使用宏把Attribute复制到Definition

在处理ER/Studio生成SQL脚本时发现，如果在Definition处没有定义，那么在生成SQL脚本后就没有表和字段的注释。

解决办法：添加一个宏，Tools-->Basic Macro Editor，录入代码：  

保存为Attribute2Definition.BAS文件，路径不要改。

使用时：右键-->Add/Remove Macro shortcuts ,选择Attribute2Definition，添加到右边列表中。再右键-->Macros-->Attribute2Definition执行即可。

 

Dim EntCount As Integer
Dim ColCount As Integer
Dim MyDiagram As Diagram
Dim MyModel As Model
Dim MyEntity As Entity
Dim MyAttribute As AttributeObj
Dim TableArray() As String
Dim ColArray() As String
Function getColumns(TableName As String )
Dim Indx As Integer
Dim count As Integer
count =1
Indx =0
Set MyEntity = MyModel.Entities.Item(TableName)
ColCount = MyEntity.Attributes.Count
ReDim ColArray(0To ColCount) As String
For count=1 To ColCount
  For Each MyAttribute In MyEntity.Attributes
    If MyAttribute.SequenceNumber = count Then
      If MyModel.Logical = True Then
        If MyAttribute.HasLogicalRoleName = True Then
          ColArray(Indx) = MyAttribute.LogicalRoleName
      Else
        ColArray(Indx) = MyAttribute.AttributeName
      End If
    Else
      If MyAttribute.HasRoleName = True Then
        ColArray(Indx) = MyAttribute.RoleName
      Else
        ColArray(Indx) = MyAttribute.ColumnName
      End If
    End If
    MyAttribute.Definition = ColArray(Indx)
    Indx= Indx +1
  End If
  Next MyAttribute
  Next count
End Function

Sub Main
Debug.Clear
Set MyDiagram = DiagramManager.ActiveDiagram
Set MyModel = MyDiagram.ActiveModel
Dim Indx As Integer
Indx =0
EntCount = MyModel.Entities.Count -1
ReDim TableArray(0To EntCount) As String
For Each MyEntity In MyModel.Entities
  If MyModel.Logical = True Then
    TableArray(Indx) = MyEntity.EntityName
  Else
    TableArray(Indx) = MyEntity.TableName
  End If
  MyEntity.Definition = TableArray(Indx)
  getColumns(TableArray(Indx))
  Indx = Indx +1
Next MyEntity
End Sub

 

 

 

 

 

 

 erstudio Definition2Attribute，反向工程

Option Explicit
ValidationMode = True
InteractiveMode = im_Batch

Dim mdl ' the current model

' get the current active model
Set mdl = ActiveModel
If (mdl Is Nothing) Then
  MsgBox "There is no current Model "
ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then
  MsgBox "The current model is not an Physical Data model. "
Else
  ProcessFolder mdl
End If

Private sub ProcessFolder(folder)
On Error Resume Next
  Dim Tab 'running   table
  for each Tab in folder.tables
    if not tab.isShortcut then
                  if tab.comment="" then 
                  else
      tab.name = tab.comment
                  end if
      Dim col ' running column
      for each col in tab.columns
      if col.comment="" then
      else
        col.name= left(col.comment,8)
      end if
      next
    end if
  next

  Dim view 'running view
  for each view in folder.Views
    if not view.isShortcut then
      view.name = view.comment
    end if
  next

  ' go into the sub-packages
  Dim f ' running folder
  For Each f In folder.Packages
    if not f.IsShortcut then
      ProcessFolder f
    end if
  Next
end sub

  

Powerdesinger name to commnet

Option Explicit 
ValidationMode = True 
InteractiveMode = im_Batch

Dim mdl ' the current model

' get the current active model 
Set mdl = ActiveModel 
If (mdl Is Nothing) Then 
  MsgBox "There is no current Model " 
ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then 
  MsgBox "The current model is not an Physical Data model. " 
Else 
  ProcessFolder mdl 
End If

' This routine copy name into comment for each table, each column and each view 
' of the current folder 
Private sub ProcessFolder(folder) 
  Dim Tab 'running   table 
  for each Tab in folder.tables 
    if not tab.isShortcut then 
      tab.comment = tab.name 
      Dim col ' running column 
      for each col in tab.columns 
        col.comment= col.name 
      next 
    end if 
  next

  Dim view 'running view 
  for each view in folder.Views 
    if not view.isShortcut then 
      view.comment = view.name 
    end if 
  next

  ' go into the sub-packages 
  Dim f ' running folder 
  For Each f In folder.Packages 
    if not f.IsShortcut then 
      ProcessFolder f 
    end if 
  Next 
end sub

 

 

Powerdesigner comment to name

Option Explicit 
ValidationMode = True 
InteractiveMode = im_Batch

Dim mdl ' the current model

' get the current active model 
Set mdl = ActiveModel 
If (mdl Is Nothing) Then 
  MsgBox "There is no current Model " 
ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then 
  MsgBox "The current model is not an Physical Data model. " 
Else 
  ProcessFolder mdl 
End If

Private sub ProcessFolder(folder) 
On Error Resume Next
  Dim Tab 'running   table 
  for each Tab in folder.tables 
    if not tab.isShortcut then 
      tab.name = tab.comment
      Dim col ' running column 
      for each col in tab.columns 
      if col.comment="" then
      else
        col.name= col.comment 
      end if
      next 
    end if 
  next

  Dim view 'running view 
  for each view in folder.Views 
    if not view.isShortcut then 
      view.name = view.comment 
    end if 
  next

  ' go into the sub-packages 
  Dim f ' running folder 
  For Each f In folder.Packages 
    if not f.IsShortcut then 
      ProcessFolder f 
    end if 
  Next 
end sub

 

Powerdesigner 自动命名主键、外键名称 

Option Explicit
ValidationMode = True
InteractiveMode = im_Batch
Dim mdl ' the current model
Set mdl = ActiveModel
If (mdl Is Nothing) Then
   MsgBox "There is no current Model"
ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then
   MsgBox "The current model is not an Physical Data model."
Else
   ProcessFolder mdl
End If

Private sub ProcessFolder(folder)
    'Tables
   Dim tab
   for each tab in folder.tables
       
       
      '自動設置主鍵
      dim ky
      for each ky in tab.Keys
            if ky.primary =true then
               ky.Name="PK_"+tab.Code
               ky.Code=ky.Name
               ky.ConstraintName=ky.Name
               'ky.Clustered=true
            end if
      next
                       
   next
   
   '自動設置外鍵
   dim ref
   for each ref in folder.References
      ref.name="FK_"+ref.ChildTable.Code +"_"+ref.ForeignKeyColumnList
      ref.Code=ref.Name
      ref.ForeignKeyConstraintName=ref.name
   next

   

   ' go into the sub-packages
   Dim f ' running folder
   For Each f In folder.Packages
      if not f.IsShortcut then
         ProcessFolder f
      end if
   Next
end sub