HogeDB内の全てのテーブルに対し、
共通項目であるCREDATEの日付けが
2日以前のデータを削除する。
結果ログ出力。
■ログ
delete from AAA where CREDATE <= '2014/11/02'
delete from BBB where CREDATE <= '2014/11/02'
delete from CCC where CREDATE <= '2014/11/02'
Option Explicit
Dim objFileSys
Dim objWshShell
Dim objText
Dim objADO
Dim objRS
Dim strSQL
Dim strOutFile
Dim strOut
dim wdate
Set objFileSys = CreateObject("Scripting.FileSystemObject")
Set objWshShell = CreateObject("WScript.Shell")
strOutFile = objWshShell.CurrentDirectory & "\log.txt"
Set objWshShell = Nothing
Set objADO = CreateObject("ADODB.Connection")
objADO.Open "Driver={SQL Server};" & _
"server=(local)\SQLExpress; database=Hoge; uid=sa; pwd=sasa;"
strSQL = "select name from sys.tables order by name"
strOut = ""
Set objRS = objADO.Execute(strSQL)
'2日前を取得
wdate = dateadd("d",-2,date())
Do Until objRS.Eof = True
strSQL = "delete from " & objRS("name") & " where CREDATE <= '" & wdate & "'"
objADO.Execute strSQL
strOut = strOut & strSQL & VbCrLf
objRS.MoveNext
Loop
objRS.Close
objADO.Close
Set objRS = Nothing
Set objADO = Nothing
Set objText = objFileSys.CreateTextFile(strOutFile)
objText.Write(strOut)
objText.Close
Set objText = Nothing
Set objFileSys = Nothing
■エビデンス
■エビデンス
