Sub 选项按钮4_Click() Dim sqlStr, sqlTemp, pkStr, columnName, columnType, columnLength, isPk, notExistTable, isNull, comment As String Dim tableListIndex, columnListIndex As Integer Dim avFilePath As String '文件路径 Dim lvIntFileNum As Integer '空文件号 Dim lvContents As String avFilePath = "D:\SQLServer.txt" '文件路径 lvIntFileNum = FreeFile() '获取一个空文件 '生成建表hql Dim sql As String Dim tableName As String Dim comm As String For si = 3 To Workbooks(1).Sheets.Count '从第三张表开始,遍历每一张表 Set mysheet = Workbooks(1).Sheets(si) '表 tableName = mysheet.Range("B1").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 comm = "" For i = 5 To mysheet.UsedRange.Rows.Count '从第五行开始遍历所有的列 Dim nameStr As String Dim typeStr As String nameStr = mysheet.Range("B" & i).Value '字段名 typeStr = mysheet.Range("C" & i).Value '数据类型 If typeStr = "int" Or typeStr = "bigint" Or typeStr = "datetime" Or typeStr = "text" Or typeStr = "image" Or typeStr = "tinyint" Then typeStr = mysheet.Range("C" & i).Value '数据类型 Else typeStr = mysheet.Range("C" & i).Value + "(" + mysheet.Range("D" & i).Value + ")" '数据类型 End If commStr = mysheet.Range("F" & i).Value '中文注释 isNull = mysheet.Range("E" & i).Value '是否为空 isPk = mysheet.Range("G" & i).Value '是否主键 sql = sql & " " & nameStr & " " & typeStr If isNull = "否" Then sql = sql + " NOT NULL" End If If isPk = "是" Then sql = sql + " PRIMARY KEY" End If If Len(commStr) > 0 Then comm = comm & vbCrLf & "EXEC sp_addextendedproperty 'MS_Description', N'" & commStr & "','SCHEMA', N'dbo','TABLE', N'" & tableName & "','COLUMN', N'" & nameStr & "';" 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 & comm & vbCrLf & "-----Create table " & tableName & " end." & vbCrLf & vbCrLf Next si lvContents = lvContents + sql Open avFilePath For Output As #lvIntFileNum '打开处理文件 Print #lvIntFileNum, (lvContents) '写文件 Close #lvIntFileNum '关闭文件 If notExistTable <> "" Then MsgBox "表" + notExistTable + "不存在！！" End If MsgBox "文件生成成功！文件路径" + avFilePath End Sub 
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/214404.html原文链接：https://javaforall.net
