多谢两位回答,可是: from 后边不一定就是表呀. 有可能是: 1.后边是多个表并用","连接 2.后边是用SELECT 语句生成. 3.后边是用join 连接.
SELECT (case when a.colorder=1 then d.name else '' end)表名, a.colorder 字段序号, a.name 字段名, (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识, (case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then '√' else '' end) 主键, b.name 类型, a.length 占用字节数, COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度, isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数, (case when a.isnullable=1 then '√'else '' end) 允许空, isnull(e.text,'') 默认值, isnull(g.[value],'') AS 字段说明 FROM syscolumns a left join systypes b on a.xtype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'left join syscomments eon a.cdefault=e.idleft join sysproperties gon a.id=g.id AND a.colid = g.smallid order by a.id,a.colorder
那道from后面的字符串后可以到数据库中去验证一下 是表 还是试图等
Public Function GetTableName(Sql, conn) As String '作用:获得所有在SQL语句中出现的表,把他传回去。 '在from 后的,和from 后边的","后的,的一个单词,join后边的单词, '得到从from 到 where 或)或ORDER,GROUP,HAVING,UNION,COMPUTE,OPTION 之间的所有字符,然后如果得到from 后的 '第一个单词,得到每一个“,”后的第一个单词,得到join 后的单词. Dim i, J, K, arr Dim TempSql1, TempSql2, TempSql3, EndTemp Sql = UCase(Sql) arr = "" '''''Debug.Print Sql For i = 1 To Len(Sql) J = J + 1 TempSql1 = 0 TempSql1 = InStr(i, Sql, "FROM") If TempSql1 <> 0 Then i = TempSql1 TempSql3 = Mid(Sql, i, Len(Sql) - i + 1) arr = arr + "@" + TempSql3 End If Next arr = Mid(arr, 2, Len(arr) - 1)arr = Split(arr, "@")'在每个from 后边都有表的存在,要提出来。On Error Resume Next Dim allTable, arrkey(10), temp4, L, temp5, temp6, tFlag tFlag = 0 For i = 0 To UBound(arr) arrkey(0) = "WHERE": arrkey(1) = "GROUP": arrkey(2) = "HAVING": arrkey(3) = "ORDER": arrkey(4) = "FROM" 'from aa a ,bb,cc,dd d join dfd df For K = 0 To 4 TempSql2 = arrkey(K) EndTemp = InStr(5, arr(i), TempSql2)
If EndTemp <> 0 Then tFlag = 1 allTable = allTable & "@" & SubTable(arr(i), 5, EndTemp) '''''Debug.Print "frstjobs:", allTable '提出每个‘,’后和JOION后的第一个单词。 '先找‘,’ temp5 = 1 For L = 0 To Len(arr(i)) '找到每一个‘,’后边的单词 temp5 = InStr(L, arr(i), ",")
'再找JOIN 找到第一个join 成功 For L = 0 To Len(arr(i)) '找到每一个‘JOIN’后边的单词 temp6 = InStr(L, arr(i), "JOIN") If temp6 <> 0 Then
'找到了,把它后边最后一个单词传回来 allTable = allTable + "@" + SubTable(arr(i), temp6 + 5, Len(arr(i))) DoEvents '在从这个位置开始找 L = temp6 End If
Next
End IfNext'所表可能的表为allTable '要测试一下,如果在查询不出错,并且表中没有现在的表,就说明是正确的表。 Dim ArrTable, arrAllTable ArrTable = Split(allTable, "@") For i = 0 To UBound(ArrTable) Err.Clear conn.Execute "select top 1 * from " & ArrTable(i) If Err.Number = 0 And InStr(arrAllTable, ArrTable(i)) = 0 Then arrAllTable = arrAllTable + "," + ArrTable(i) End If
Next '这样arrAllTable就是所要的表了。除去最前边的@就可以返回了。 GetTableName = Mid(arrAllTable, 2, Len(arrAllTable) - 1) End FunctionPublic Function SubTable(Sql, statNum, endNum) As String '找到给定的区域内的第一个单词 Dim K '查询出内包括的表。 Dim temp1, t, t1, temp2, m For K = statNum To endNum '找到第一个表名 If Mid(Sql, K, 1) = " " Or Mid(Sql, K, 1) = "," Or Mid(Sql, K, 1) = "." Then t = 0 'temp1 = temp1 & Mid(Sql, K, 1) If t = 0 And t1 = 1 Then
temp1 = temp1 & Mid(Sql, K, 1) 'Debug.Print "sssdd", Trim(Mid(temp1, 1, Len(temp1) - 1)) '删除可能存在的( ) 'Debug.Print "left:", Left(temp1, 1) For m = 0 To 3 If Left(temp1, 1) = "(" Or Left(temp1, 1) = "{" Or Left(temp1, 1) = "[" Then
temp1 = Mid(temp1, 2, Len(temp1))
DoEvents
End If
If Right(temp1, 1) = "(" Or Right(temp1, 1) = "{" Or Right(temp1, 1) = "[" Then
用VB
'在from 后的,和from 后边的","后的,的一个单词,join后边的单词,
'得到从from 到 where 或)或ORDER,GROUP,HAVING,UNION,COMPUTE,OPTION 之间的所有字符,然后如果得到from 后的
'第一个单词,得到每一个“,”后的第一个单词,得到join 后的单词.
'把这些单词记录下来就是所表的表.
from 后边不一定就是表呀.
有可能是:
1.后边是多个表并用","连接
2.后边是用SELECT 语句生成.
3.后边是用join 连接.
'作用:获得所有在SQL语句中出现的表,把他传回去。
'在from 后的,和from 后边的","后的,的一个单词,join后边的单词,
'得到从from 到 where 或)或ORDER,GROUP,HAVING,UNION,COMPUTE,OPTION 之间的所有字符,然后如果得到from 后的
'第一个单词,得到每一个“,”后的第一个单词,得到join 后的单词.
Dim i, J, K, arr
Dim TempSql1, TempSql2, TempSql3, EndTemp
Sql = UCase(Sql)
arr = ""
'''''Debug.Print Sql
For i = 1 To Len(Sql)
J = J + 1
TempSql1 = 0
TempSql1 = InStr(i, Sql, "FROM")
If TempSql1 <> 0 Then
i = TempSql1
TempSql3 = Mid(Sql, i, Len(Sql) - i + 1)
arr = arr + "@" + TempSql3
End If
Next
arr = Mid(arr, 2, Len(arr) - 1)arr = Split(arr, "@")'在每个from 后边都有表的存在,要提出来。On Error Resume Next
Dim allTable, arrkey(10), temp4, L, temp5, temp6, tFlag
tFlag = 0
For i = 0 To UBound(arr)
arrkey(0) = "WHERE": arrkey(1) = "GROUP": arrkey(2) = "HAVING": arrkey(3) = "ORDER": arrkey(4) = "FROM" 'from aa a ,bb,cc,dd d join dfd df For K = 0 To 4
TempSql2 = arrkey(K)
EndTemp = InStr(5, arr(i), TempSql2)
If EndTemp <> 0 Then tFlag = 1
allTable = allTable & "@" & SubTable(arr(i), 5, EndTemp)
'''''Debug.Print "frstjobs:", allTable
'提出每个‘,’后和JOION后的第一个单词。
'先找‘,’
temp5 = 1
For L = 0 To Len(arr(i))
'找到每一个‘,’后边的单词
temp5 = InStr(L, arr(i), ",")
If temp5 <> 0 Then
'找到了,把它后边最后一个单词传回来
allTable = allTable + "@" + SubTable(arr(i), temp5, Len(arr(i)))
DoEvents
'''''Debug.Print L
'在从这个位置开始找
L = temp5
End If
Next
'再找JOIN 找到第一个join 成功
For L = 0 To Len(arr(i))
'找到每一个‘JOIN’后边的单词
temp6 = InStr(L, arr(i), "JOIN")
If temp6 <> 0 Then
'找到了,把它后边最后一个单词传回来
allTable = allTable + "@" + SubTable(arr(i), temp6 + 5, Len(arr(i)))
DoEvents
'在从这个位置开始找
L = temp6
End If
Next
End If
Next
If tFlag = 0 Then
'什么也没有
allTable = allTable & "@" & SubTable(arr(i), 5, Len(arr(i)))
' '''''Debug.Print arr(i)
For L = 0 To Len(arr(i))
'找到每一个‘,’后边的单词
temp5 = InStr(L, arr(i), ",")
If temp5 <> 0 Then
'找到了,把它后边最后一个单词传回来
allTable = allTable + "@" + SubTable(arr(i), temp5, Len(arr(i)))
DoEvents
'在从这个位置开始找
L = temp5
End If
Next
'再找JOIN 找到第一个join 成功
For L = 0 To Len(arr(i))
'找到每一个‘JOIN’后边的单词
temp6 = InStr(L, arr(i), "JOIN")
If temp6 <> 0 Then
'找到了,把它后边最后一个单词传回来
allTable = allTable + "@" + SubTable(arr(i), temp6 + 5, Len(arr(i)))
DoEvents
'在从这个位置开始找
L = temp6
End If
Next
End IfNext'所表可能的表为allTable
'要测试一下,如果在查询不出错,并且表中没有现在的表,就说明是正确的表。
Dim ArrTable, arrAllTable
ArrTable = Split(allTable, "@")
For i = 0 To UBound(ArrTable)
Err.Clear
conn.Execute "select top 1 * from " & ArrTable(i)
If Err.Number = 0 And InStr(arrAllTable, ArrTable(i)) = 0 Then
arrAllTable = arrAllTable + "," + ArrTable(i)
End If
Next
'这样arrAllTable就是所要的表了。除去最前边的@就可以返回了。
GetTableName = Mid(arrAllTable, 2, Len(arrAllTable) - 1)
End FunctionPublic Function SubTable(Sql, statNum, endNum) As String
'找到给定的区域内的第一个单词
Dim K '查询出内包括的表。
Dim temp1, t, t1, temp2, m
For K = statNum To endNum
'找到第一个表名
If Mid(Sql, K, 1) = " " Or Mid(Sql, K, 1) = "," Or Mid(Sql, K, 1) = "." Then
t = 0
'temp1 = temp1 & Mid(Sql, K, 1)
If t = 0 And t1 = 1 Then
temp1 = temp1 & Mid(Sql, K, 1)
'Debug.Print "sssdd", Trim(Mid(temp1, 1, Len(temp1) - 1))
'删除可能存在的( )
'Debug.Print "left:", Left(temp1, 1)
For m = 0 To 3
If Left(temp1, 1) = "(" Or Left(temp1, 1) = "{" Or Left(temp1, 1) = "[" Then
temp1 = Mid(temp1, 2, Len(temp1))
DoEvents
End If
If Right(temp1, 1) = "(" Or Right(temp1, 1) = "{" Or Right(temp1, 1) = "[" Then
temp1 = Mid(temp1, 2, Len(temp1))
DoEvents
End If
Next
'''Debug.Print "dd", Trim(Mid(temp1, 1, Len(temp1) - 1))
SubTable = Trim(Mid(temp1, 1, Len(temp1) - 1))
'Debug.Print "st:", SubTable
Exit For
End If
Else
'向下走
temp1 = temp1 & Mid(Sql, K, 1)
t1 = 1
End If
Next
End Function