设计数据库表时需要为每列加上备注,通常列的name就是备注,但是在生成SQL时PowerDesigner不会将name作为列备注,使用vb脚本可实现将表的列的name和comment互转

菜单Tools->Execute Commands->Edit/Run Scripts...->打开对应vbs脚本文件即可执行

对脚本进行一定修改,可以实现字段code转小写,增加预定义字段,设置字段属性等

name2comment.vbs

'******************************************************************************
'* File:     name2comment.vbs
'* Purpose:   Database generation cannot use object names anymore
'         in version 7 and above.
'         It always uses the object codes.
'
'         In case the object codes are not aligned with your
'         object names in your model, this script will copy
'         the object Name onto the object Comment for
'         the Tables and Columns.
'
'* Title:
'* Version:   1.0
'* Company:   Sybase Inc.
'******************************************************************************
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
			' 判断是否含必须字段列,如果没有则添加
			Dim idAdd
			Dim createdTimeAdd
			Dim modifiedTimeAdd
			Dim remarkAdd
			Dim ynAdd
			idAdd = False
			createdTimeAdd = False
			modifiedTimeAdd = False
			remarkAdd = False
			ynAdd = False
			For Each col In tab.columns
				Dim code 
				col.code = LCase(col.code)
				code = col.code
				' 如果备注包含',1,' 则认为是枚举类型,字段类型设置为tinyint
				if(InStr(col.comment, ",1,")) then
					col.datatype = "tinyint"
				end if
				If(code = "id") Then
					idAdd = True
				ElseIf(code = "created_time") Then
					createdTimeAdd = True
				ElseIf(code = "modified_time") Then
					modifiedTimeAdd = True
				ElseIf(code = "remark") Then
					remarkAdd = True
				ElseIf(code = "yn") Then
					ynAdd = True
				End If 
			Next
			Dim colToAdd
			If(idAdd = False) Then
				'http://nntp-archive.sybase.com/nntp-archive/action/article/%3C44db862f.601e.1681692777%40sybase.com%3E
				Set colToAdd = Tab.CreateObject(PdPDM.cls_Column,,-1)
				colToAdd.code = "id"
			End If 
			If(createdTimeAdd = False) Then
				Set colToAdd = Tab.CreateObject(PdPDM.cls_Column,,-1)
				colToAdd.code = "created_time"
			End If 
			If(modifiedTimeAdd = False) Then
				Set colToAdd = Tab.CreateObject(PdPDM.cls_Column,,-1)
				colToAdd.code = "modified_time"
			End If 
			If(remarkAdd = False) Then
				Set colToAdd = Tab.CreateObject(PdPDM.cls_Column,,-1)
				colToAdd.code = "remark"
			End If 
			If(ynAdd = False) Then
				Set colToAdd = Tab.CreateObject(PdPDM.cls_Column,,-1)
				colToAdd.code = "yn"
			End If 
			For Each col In tab.columns
				code = col.code
				If(code = "id") Then 
					col.name = "主键"
					col.datatype = "bigint(20)"
					col.length = 20
					col.identity = True
					col.primary = True
					'col.mandatory = True '设置了主键就不能设置mandatory值,因为主键会默认设置非空
				ElseIf(code = "remark") Then 
					col.name = "备注"
					col.datatype = "varchar(255)"
					col.length = 255
					col.defaultvalue = "NULL"
				ElseIf(code = "created_time") Then 
					col.name = "创建时间"
					col.datatype = "datetime"
					col.defaultvalue = "CURRENT_TIMESTAMP"
				ElseIf(code = "modified_time") Then 
					col.name = "修改时间"
					col.datatype = "timestamp"
					col.defaultvalue = "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
				ElseIf(code = "yn") Then
					col.name = "是否有效" 
					col.datatype = "int(11)"
					col.length = 11
					col.comment = "是否有效,1,有效,0,无效"
				End If
				'decimal默认decimal(18,4)
				If(col.datatype = "decimal") Then
					col.datatype="decimal(18,4)"
					col.length = 18
					col.precision = 4
				End If
				'默认int(11)
				If(col.datatype = "int") Then
					col.datatype="int(11)"
					col.length = 11
				End If
				'数据库备注字段,如果为空则设置为name值
				If(col.comment = "") Then
					col.comment= col.name
				End If
				'默认值
				If(col.defaultvalue = "" And col.code <> "id" And col.primary <> True And col.identity <> True) Then
					col.defaultvalue = "NULL"
				End If
				'数据类型
				If(col.datatype = "") Then
					col.datatype = "varchar(255)"
					col.length = 255
				End If
			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

comment2name.vbs(常用于逆向生成)

'******************************************************************************
'* File:     comment2name.vbs
'* Purpose:   Database generation cannot use object names anymore
'         in version 7 and above.
'         It always uses the object codes.
'
'         In case the object codes are not aligned with your
'         object names in your model, this script will copy
'         the object Comment onto the object Name for
'         the Tables and Columns.
'
'* Title:
'* Version:   1.0
'* Company:   Sybase Inc.
'******************************************************************************
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		
			'表备注
			If(tab.comment <> "") Then
				Tab.name = tab.comment
			End If
			Dim col ' running column
			For Each col In tab.columns
				If(col.comment <> "") Then
					'数据库备注字段
					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.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