excel生成oracle sql语句

数据资产盘点工作中，对系统的各个表内容(包括表名、字段名等信息)进行了收集，但需要将这些信息转成需要的oracle sql语句，从而可以批量导入PowerDesigner软件中进行数据库的概念模型和物理模型设计，以减少工作量。

将代码Copy到excel的代码框中执行就可以了。

'生成表目录
Private Sub btn_shell_Click()
For si = 2 To Workbooks(1).Sheets.Count '从第二张表开始,遍历每一张表
        Set mysheet = Workbooks(1).Sheets(si) '表
        Workbooks(1).Sheets(1).Range("D" & si).Value = mysheet.Name '中文名
        Workbooks(1).Sheets(1).Range("C" & si).Value = mysheet.Range("A2").Value  '英文名
Next si
End Sub
'生成建表hql
Private Sub btnSql_Click()
    Dim sql As String
    Dim tableName As String
    
    For si = 2 To Workbooks(1).Sheets.Count '从第二张表开始,遍历每一张表

        Set mysheet = Workbooks(1).Sheets(si) '表
        tableName = mysheet.Range("A2").Value '英文表名
        
        
        '如果数据库中已存在表，则删除表
        'if exists (select * from sysobjects where name='tableName')
        sql = sql & "if exists (select * from sysobjects where name='" & tableName & "') " & vbCrLf
        
        'drop table tableName
        sql = sql & "   drop table " & tableName & " " & vbCrLf
        sql = sql & "go " & vbCrLf
        
        '开始创建表
        'create table tableName (
        sql = sql & "create table " & tableName & " ( " & vbCrLf
            
        
        For i = 2 To mysheet.UsedRange.Rows.Count '遍历所有的列
            Dim nameStr As String
            Dim typeStr As String
        
            nameStr = mysheet.Range("B" & i).Value '字段名
            typeStr = mysheet.Range("D" & i).Value '数据类型
            refStr = mysheet.Range("E" & i).Value '引用
            commStr = mysheet.Range("C" & i).Value '中文注释'
            If nameStr = "ID" Then
                sql = sql & "    id  varchar(44) primary key" '默认id为主键
            Else
                sql = sql & "   " & nameStr & " " & typeStr '非id列,则直接加入
            End If
            
            If Len(commStr) > 0 Then
                    comm = comm & vbCrLf & "comment on column " & tableName & "." & nameStr & "  is " & "'" & commStr & "';"
            End If
            
            If i < mysheet.UsedRange.Rows.Count Then
                sql = sql & "," '添加‘,’号
            End If
            
             sql = sql & vbCrLf
             
        Next i
        
        '建表完成
        sql = sql & ")" & vbCrLf
        sql = sql & "go" & vbCrLf
        sql = sql & "-----Create table " & tableName & " end." & vbCrLf & vbCrLf
        sql = sql & comm & Chr(13) & Chr(10) & Chr(10) & Chr(10) & Chr(10) & Chr(10)
        comm = ""
    
    Next si
    txtConsole.Value = sql '将值输出到console控件中
End Sub

Private Sub txtConsole_Change()

End Sub