mysql run sql files_vbs做的sql脚本执行工具

灰色小熊

于 2021-02-07 12:12:43 发布

阅读量260

点赞数

文章标签： mysql run sql files

本文链接：https://blog.csdn.net/weixin_28523493/article/details/113910452

版权

##################执行多目录下的所有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

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

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

'End If

Else

MsgBox "目录 " + file_path + "不存在！"

End If

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

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

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'预留的执行空闲等待

'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)

'日志记录结束时间

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)

end Function

Public function showFiles()

For i = 0 To UBound(files) - 1

MsgBox CStr(i) + "=" + files(i)

end function

End Class

todo

灰色小熊

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql run sql files_vbs做的sql脚本执行工具

##################执行多目录下的所有sql脚本的工具#################################'#可以方便的执行指定的目录或文件到多个服务器多个数据库'#只会执行以“.sql”后缀的脚本文件'#执行返回结果会生成报告以文本文件打开'【请在此过程中填写运行参数】Sub todoDim execToolSet execTool=new MySqlScriptE...
复制链接

扫一扫