注册
登录
| 关键词： nbsp function length 文件 tmprstfilepathId files databaseName Public 目录 file |
##################执行多目录下的所有sql脚本的工具#################################'#可以方便的执行指定的目录或文件到多个服务器多个数据库'#只会执行以“.sql”后缀的脚本文件'#执行返回结果会生成报告以文本文件打开 '【请在此过程中填写运行参数】Sub todoDim execToolSet 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 fsoPrivate WshShell'Private tempRstFileNamePrivate 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.CloseEnd 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.CloseEnd Function '读运文本文件内容Private Function readTxtFile(file_path) Const ForReading = 1, ForWriting = 2 Dim MyFile Set MyFile = fso.OpenTextFile(file_path, ForReading) readTxtFile = MyFile.ReadAll() MyFile.CloseEnd 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 = falseend 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 Nextend 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 Nextend 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 Nextend 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 " + logoutFileend Function Public function showFolds() For i = 0 To UBound(folds) - 1 MsgBox CStr(i) + "=" + folds(i) Nextend Function Public function showFiles() For i = 0 To UBound(files) - 1 MsgBox CStr(i) + "=" + files(i) Nextend function End Class todo |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
