EXCEL生成建表SQL的脚本（SQL Server）

 

EXCEL模板，此处使用第二个SHEET

Sub createSingle_Click()
Dim sql As String
Dim tableName As String
Dim comm As String

Set fs = CreateObject("Scripting.FileSystemObject")
sFilePath = ActiveWorkbook.Path & "\Script\"
If Dir(sFilePath, vbDirectory) = "" Then
    MkDir sFilePath
End If

sFileName = sFilePath & "Create_DHR_CF_Table_Script.sql"
Set fhandle = fs.CreateTextFile(sFileName, True)

fhandle.WriteLine ("--表结构创建脚本，对应数据库sqlserver")
fhandle.WriteLine ("--建表脚本创建开始:" & Date & " " & Time)


Set mysheet = Workbooks(1).Sheets(2) '第二张表
tableName = ""

For i = 2 To mysheet.UsedRange.Rows.Count '遍历所有的行
Dim nameStr As String
Dim typeStr As String
Dim isNullStr As String
Dim lengthStr As String
Dim refStr As String

If tableName = mysheet.Range("A" & i).Value Then
    sql = sql & "," '添加‘,’号
Else
    If tableName = "" Then
        sql = sql
    Else
        sql = sql & ");" '建表完成
        sql = sql & comm & vbCrLf & "-----Create table " & tableName & " end." & vbCrLf & vbCrLf
    End If

    tableName = mysheet.Range("A" & i).Value '英文表名
    sql = sql & "IF EXISTS(Select 1 From Sysobjects Where Name='" & tableName & "')  --查询表名" & tableName & "是否存在" & vbCrLf
    sql = sql & "DROP table " & tableName & "     --存在则删除" & vbCrLf
    sql = sql & vbCrLf '开始创建表
    'create table tableName (
    sql = sql & "create table " & tableName & "  ( "
    comm = ""
End If

nameStr = mysheet.Range("B" & i).Value '字段名
typeStr = mysheet.Range("C" & i).Value '数据类型
lengthStr = mysheet.Range("D" & i).Value '长度
isNullStr = mysheet.Range("E" & i).Value '是否允许为空
refStr = mysheet.Range("F" & i).Value '是否主键
commStr = mysheet.Range("G" & i).Value & " " & mysheet.Range("H" & i).Value '中文注释

sql = sql & "  " & nameStr & "  " & typeStr

If typeStr = "datetime" Then
    sql = sql
Else
    sql = sql & "(" & lengthStr & ")" & " "
End If
                
If refStr = "Y" Then
    sql = sql & "  primary key" '主键
Else
    sql = sql '非主键,则直接加入
End If

If isNullStr = "N" Then
    sql = sql & "  not null" '非空
Else
    sql = sql '允许为空，直接加入
End If

If Len(commStr) > 0 Then '字段注释
    If commStr = " " Then
        comm = comm
    Else
        comm = comm & vbCrLf & "EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value = N'" & commStr & "'" & vbCrLf
        comm = comm & ", @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'" & tableName & "'" & vbCrLf
        comm = comm & ",@level2type=N'COLUMN',@level2name=N'" & nameStr & "'" & vbCrLf
    End If
End If

Next i

fhandle.WriteLine (sql)
fhandle.WriteLine (" ")

fhandle.WriteLine ("")
fhandle.WriteLine ("--END;")
fhandle.WriteLine ("--/")

fhandle.Close

MsgBox "表结构创建脚本成功！文件名" & sFileName

End Sub