##################执行多目录下的所有sql脚本的工具#################################'#可以方便的执行指定的目录或文件到多个服务器多个数据库'#只会执行以“.sql”后缀的脚本文件'#执行返回结果会生成报 ...
##################执行多目录下的所有sql脚本的工具#################################
'#可以方便的执行指定的目录或文件到多个服务器多个数据库
'#只会执行以“.sql”后缀的脚本文件
'#执行返回结果会生成报告以文本文件打开
'【请在此过程中填写运行参数】
Sub todo
Dim execTool
Set execTool=new MySqlScriptExecTool
'使用步骤：
'一、指定需要执行脚本的服务器，分别为：目标服务器的ip、数据库名、数据库用户名、数据库密码
execTool.addDatabaseLink "192.168.1.116","dbname","username","pwd"
'如果需要添加多个不同的服务器或者数据库，可以模仿如下方法继续添加
'execTool.addDatabaseLink "192.168.1.116","dbname2","username","pwd"
'二、指定需要执行的脚本所在的目录：这样在最终执行前会搜索出具体的需要执行的文件，可以写多条类似语句添加多个目录
execTool.addFold "F:\exec\sql"
'execTool.addFold "D:\drp\DEV\db\SQLServer\report"
'三、如果同时需要执行目录下面有子目录的脚本则需要去掉下面这句话前面的“'”
'execTool.searchChildFolds()'迭代找出所有子目录
'找出指定目录里面的sql脚本
execTool.searchFiles()
'四、也可以单独需要执行的文件
'execTool.addFile("D:\drp\DEV\db\SQLServer\common\init.sql")
'开始执行脚本到服务器
execTool.execSqlScript()
End Sub
Class MySqlScriptExecTool
'属性
Public folds()'sql语句所在目录
Public files()'文件
Public logoutFile'日志输出文件
Private fso
Private WshShell
'Private tempRstFileName
Private currentFolder'当前目录
Private DBserver()'指定数组大小则后面不能该大小了，要改大小的时候报错：该数组为定长的或临时被锁定
Private databaseName()
Private username()
Private password()
'初始化
Private Sub Class_Initialize
ReDim folds(0)
ReDim files(0)
ReDim DBserver(0)
ReDim databaseName(0)
ReDim username(0)
ReDim password(0)
Set fso = Wscript.CreateObject("Scripting.FileSystemObject")
set WshShell = WScript.CreateObject("WScript.Shell")
strPath = Wscript.ScriptFullName
objFile = fso.GetFile(strPath)
currentFolder = fso.GetParentFolderName(objFile)
logoutFile = currentFolder + "\SqlScriptExecTool_Log.txt"
End Sub
'添加目标数据库
Public function addDatabaseLink(DBserverip,dbname, usenme2, pwd)
length = UBound(DBserver)
DBserver(length) = DBserverip: ReDim Preserve DBserver(length + 1)
databaseName(length) = dbname: ReDim Preserve databaseName(length + 1)
username(length) = usenme2: ReDim Preserve username(length + 1)
password(length) = pwd: ReDim Preserve password(length + 1)
end Function
'创建文件，存在则覆盖
Public function createNewFile(dscFile, msgs)
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim f
Set f = fso.OpenTextFile(dscFile, ForWriting, True)
f.Write msgs
f.Close
End Function
'追加文件
Public function appendToFile(dscFile, msgs)
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim f
Set f = fso.OpenTextFile(dscFile, ForAppending, True)
f.Write msgs
f.Close
End Function
'读运文本文件内容
Private Function readTxtFile(file_path)
Const ForReading = 1, ForWriting = 2
Dim MyFile
Set MyFile = fso.OpenTextFile(file_path, ForReading)
readTxtFile = MyFile.ReadAll()
MyFile.Close
End Function
'添加目录，有避免重复的检查
Public function addFold(fold)
If fso.FolderExists(fold)=False Then
MsgBox "文件夹"+fold+"不存在！"
Exit Function
End If
length = UBound(folds)
For i = 0 To length - 1
If fold = folds(i) Then Exit Function
Next
folds(length) = fold: ReDim Preserve folds(length + 1)
end Function
'添加文件，有避免重复的检查
Public function addFile(file)
If fso.FileExists(file)=False Then
MsgBox "文件"+file+"不存在！"
Exit Function
End If
'If isSqlScriptFile(file) = false Then Exit Function
length = UBound(files)
For i = 0 To length - 1
If file = files(i) Then Exit Function
Next
files(length) = file: ReDim Preserve files(length + 1)
end Function
Public function isSqlScriptFile(file)
If Len(file) > 4 And UCase(Right(file,4)) = ".SQL" Then
isSqlScriptFile = true
Exit Function
End If
isSqlScriptFile = false
end Function
'找出目录下的sql文件
Public function searchFiles()
length = UBound(folds)
For i = 0 To length - 1
file_path = folds(i)
'MsgBox "分析文件 "+CStr(i)+"="+folds(i)
If fso.FolderExists(file_path) = true Then
'MsgBox "存在的!"
Dim fold
Set fold = fso.GetFolder(file_path)
'If fold.files.Count > 0 Then
For Each flde In fold.files
If isSqlScriptFile(flde.Path) Then
'MsgBox flde.Path 'Name Path
addFile flde.Path
End If
Next
'End If
Else
MsgBox "目录 " + file_path + "不存在！"
End If
Next
end Function
'找出所有目录
Public function searchChildFolds()
'If searchChildFold = False Then Exit Function
length=UBound(folds)+1
For i=length-1 To 0 Step -1
file_path=folds(i)
'MsgBox "分析文件 "+CStr(i)+"="+folds(i)
If fso.FolderExists(file_path)=true Then
'MsgBox "存在!"
Dim fold
Set fold = fso.GetFolder(file_path)
ListAllChildFolds fold
End If
Next
end Function
'迭代找出所有子文件夹
Public function ListAllChildFolds(fold)
If fold.SubFolders.Count =0 Then Exit Function
For Each subFolder In fold.SubFolders'fold.Files
'将子层目录加入待处理目录
'MsgBox subFolder.Path
addFold subFolder.Path
Dim childFold
Set childFold = fso.GetFolder(subFolder.Path)
ListAllChildFolds childFold
Next
end Function
Private Function getTempRstFileName(index, scriptName, serverName, databaseName)
'getTempRstFileName = currentFolder + "" + CStr(index) + "@" + serverName + "@" + databaseName + "@" + scriptName + ".txt"
getTempRstFileName = currentFolder + "" + serverName + "@" + databaseName + "@" + scriptName + ".txt"
End Function
Public function execSqlScript()
'创建日志文件
createNewFile logoutFile, "执行sql脚本，开始于："+CStr(Date())+" "+CStr(Time())+vbCrLf+"=============================================================="+vbCrLf
Dim tmprstfilepathIdx, tmpFileName
'1.执行
tmprstfilepathIdx = 0
For sIndex = 0 To UBound(DBserver) - 1
For i =0 To UBound(files) - 1
tmprstfilepathIdx = tmprstfilepathIdx + 1
tmpFileName = getTempRstFileName(tmprstfilepathIdx, fso.GetBaseName(files(i)), DBserver(sIndex), databaseName(sIndex))
runsql="isqlw -S " + DBserver(sIndex) + " -d " + databaseName(sIndex) + " -U " + username(sIndex) + " -P " + password(sIndex) + " -i "+files(i)+" -o " + tmpFileName
'MsgBox runsql
WshShell.Run runsql
WScript.Sleep 1000'预留的执行空闲等待
Next
Next
'2.log合并
tmprstfilepathIdx = 0
Dim newmsg
For sIndex = 0 To UBound(DBserver) - 1
For i =0 To UBound(files) - 1
tmprstfilepathIdx = tmprstfilepathIdx + 1
tmpFileName = getTempRstFileName(tmprstfilepathIdx, fso.GetBaseName(files(i)), DBserver(sIndex), databaseName(sIndex))
Dim runRst
runRst = readTxtFile(tmpFileName)
WScript.Sleep 450
newmsg = ""
If i <> 0 And sIndex <> 0 Then
newmsg = vbCrLf + vbCrLf
End If
newmsg = newmsg + "执行序号：" + CStr(tmprstfilepathIdx)
newmsg = newmsg + vbCrLf + "目 标：" + databaseName(sIndex) + "@" + DBserver(sIndex)
newmsg = newmsg + vbCrLf + "执行脚本：" + fso.GetBaseName(files(i)) + ".sql"
newmsg = newmsg + vbCrLf + "执行结果:" + vbCrLf + runRst + vbCrLf
appendToFile logoutFile, newmsg
WScript.Sleep 500
fso.DeleteFile(tmpFileName)
Next
Next
'日志记录结束时间
appendToFile logoutFile, vbCrLf + "==============================================================" + vbCrLf + "执行sql脚本，结束于：" + CStr(Date()) + " " + CStr(Time())
'用记事本打开日志文件
WshShell.Run "notepad " + logoutFile
end Function
Public function showFolds()
For i = 0 To UBound(folds) - 1
MsgBox CStr(i) + "=" + folds(i)
Next
end Function
Public function showFiles()
For i = 0 To UBound(files) - 1
MsgBox CStr(i) + "=" + files(i)
Next
end function
End Class
todo
