程序不算复杂,主要是就遍历固定格式的excel表格,然后再按sql的格式生成相应代码就是了.免去在sql server管理器里操作,效率高一点.用excel作数据字典以后打印或者查找也方便,呵呵.
数据字典结构如下图所示:
各列的位置固定,主要从第"3"列开始,在VBA里也可以设置.
另外在程序里默认把ID列作为了自增长的列处理,如有不同可相应修改.
程序代码:
Code1 Sub GenSQL()
2 '数据类型定义
3 Const I = "int"
4 Const C = "nvarchar"
5 Const D = "decimal"
6 Const T = "datetime"
7 '变量定义
8 Dim tRow As Integer '循环行变量
9 Dim tCol As Integer '循环列变量
10 Dim tableNameCol As Integer '表名列号
11 Dim fileName As String '保存文件名
12 Dim tableName As String '数据库表名
13 Dim sql As String 'sql语句
14 Dim PRIMARY_KEY As String '存储主键列表
15 '初始化
16 tRow = 3
17 tableNameCol = 3
18 fileName = "c:\sql" & DateTime.Date & ".sql"
19
20 '大循环，遍历整个文档,以"结束"二字作为程序结束标识符
21 Do Until (Sheet1.Cells(tRow, tableNameCol).Value = "结束")
22 '检测是否为一个新表，以关键字“编码”来确定
23 If (Sheet1.Cells(tRow, tableNameCol).Value = "编码") Then
24 '得到数据表名
25 tableName = Sheet1.Cells(tRow - 1, tableNameCol).Value
26 '生成sql语句
27 sql = "create table [dbo].[" & tableName & "] (" & Chr(10)
28 PRIMARY_KEY = "["
29 '移动行光标到内容行
30 tRow = tRow + 1
31 tCol = tableNameCol
32 '行遍历
33 Do Until (Len(Sheet1.Cells(tRow, tCol).Value) < 1)
34 '字段名
35 sql = sql & "[" & Sheet1.Cells(tRow, tCol).Value & "] ["
36 '取得数据类型
37 Select Case (Sheet1.Cells(tRow, tCol + 1).Value)
38 Case "I"
39 sql = sql & I & "] "
40 'ID列作为自增长的类型处理
41 If (Sheet1.Cells(tRow, tCol).Value = "ID") Then
42 sql = sql & "IDENTITY(1,1) "
43 End If
44 Case "C"
45 sql = sql & C & "] (" & Sheet1.Cells(tRow, tCol + 2).Value & ") "
46 Case "D"
47 sql = sql & D & "] (" & Sheet1.Cells(tRow, tCol + 2).Value & "," & Sheet1.Cells(tRow, tCol + 3).Value & ") "
48 Case "T"
49 sql = sql & T & "] "
50 Case Else
51 MsgBox "字段类型错误！发生于第" & tRow & "行"
52 Exit Sub
53 End Select
54 '判断是否可空
55 sql = sql & isNullAllowed(tRow, tCol + 5)
56 sql = sql & Chr(10)
57 '查找主键列
58 If (Sheet1.Cells(tRow, tCol + 4).Value = "Y") Then
59 PRIMARY_KEY = PRIMARY_KEY & Sheet1.Cells(tRow, tCol).Value & "],["
60 End If
61 tRow = tRow + 1
62 Loop
63 '去掉后面的","并添加结束字符
64 sql = Left(sql, Len(sql) - 2) & ")" & Chr(10) & "ON [PRIMARY]"
65 sql = sql & Chr(10) & "GO" & Chr(10)
66
67 sql = sql & "ALTER TABLE [dbo].[" & tableName & "] WITH NOCHECK ADD CONSTRAINT [PK_" & _
68 tableName & "] PRIMARY KEY CLUSTERED (" & Chr(10)
69 PRIMARY_KEY = Left(PRIMARY_KEY, Len(PRIMARY_KEY) - 2)
70 sql = sql & PRIMARY_KEY & Chr(10) & ") ON [PRIMARY]" & Chr(10) & "GO" & Chr(10)
71 ' MsgBox sql
72 SaveFile sql, fileName
73 End If
74
75 tRow = tRow + 1
76 Loop
77 MsgBox "生成成功!文件位于:" & fileName
78 End Sub
79
80 '以是否可空行,列坐标为参数判断是否字段可空,并返回相应的sql说明
81 Function isNullAllowed(tRow, tCol) As String
82 If (Sheet1.Cells(tRow, tCol).Value = "N") Then
83 isNullAllowed = " NOT NULL,"
84 Else
85 isNullAllowed = " NULL,"
86 End If
87 End Function
88
89 '保存sql语句,若已存在文件则直接追加,若文件不存在在先新建
90 Sub SaveFile(sql As String, fileName As String)
91 Dim fso, MyFile
92 Set fso = CreateObject("Scripting.FileSystemObject")
93 If (fso.fileExists(fileName)) Then
94 '参数8表示在文件末尾追加写入
95 Set MyFile = fso.OpenTextFile(fileName, 8)
96 Else
97 'ture表示覆盖创建
98 Set MyFile = fso.CreateTextFile(fileName, ture)
99 End If
100 MyFile.writeline (sql)
101 MyFile.Close
102 Set fso = Nothing
103 Set MyFile = Nothing
104 End Sub
/Files/erqie/Book2.zip