DDL生成
ここ最近、広く浅く調べることが多く、なかなか身につかずすぐに忘れていく。まー簡単なことならすぐに調べられるのでいいんだけどね。
VBScriptからSQLDMOを使ってDDL文(変なコードも入ってるけど)を生成する。もらった定義書が信用できないときに結果を送ってもらいます(´Д`;)ただSQL ServerのEnterprise ManagerやMSDEでもフリーなGUIツールで生成可能なので役に立つのはかなりのレアケースだヽ(`Д´)ノ
' ' genddl.vbs ' Option Explicit ' Const SQLDMOScript_Default = 4 Const SQLDMOScript_ToFileOnly = 64 Const SQLDMOScript_AppendToFile = 256 Const SQLDMOScript_Indexes = 73736 ' Dim oArgs, oPswd Dim sUser, sPswd, sFile ' Set oArgs = WScript.Arguments If oArgs.Count >= 3 Then sUser = oArgs(0) sPswd = oArgs(1) sFile = oArgs(2) Else WScript.StdOut.Write "login id> " sUser = WScript.StdIn.ReadLine WScript.StdOut.Write "password> " ' Windows XP or later Set oPswd = WScript.CreateObject("ScriptPW.Password") sPswd = oPswd.GetPassword() WScript.Echo ' sPswd = WScript.StdIn.ReadLine WScript.StdOut.Write "output file> " sFile = WScript.StdIn.ReadLine End If ' GenDDL "localhost", sUser, sPswd, sFile ' Sub GenDDL(host, user, pswd, file) Dim oServer, oDatabase, oTable Dim nDBType, nTBLType Dim ii, jj ' Set oServer = WScript.CreateObject("SQLDMO.SQLServer") oServer.Connect host, user, pswd nDBType = SQLDMOScript_Default Or SQLDMOScript_ToFileOnly nTBLType = SQLDMOScript_Default Or SQLDMOScript_ToFileOnly _ Or SQLDMOScript_AppendToFile Or SQLDMOScript_Indexes ' For ii = 1 To oServer.Databases.Count Set oDatabase = oServer.Databases(ii) If Not oDatabase.SystemObject Then oDatabase.Script nDBType, file ' For jj = 1 To oDatabase.Tables.Count Set oTable = oDatabase.Tables(jj) If Not oTable.SystemObject Then oTable.Script nTBLType, file End If Next End If Next ' oServer.DisConnect End Sub
c:\>cscript genddl.vbs login id> sa password> output file> zzz.sql c:\>type zzz.sql CREATE DATABASE [tmpdb] ... ... CREATE TABLE [tmptbl] ( ... ... GO
- Windows Script Host の基礎/http://www.microsoft.com/japan/msdn/library/default.asp?url=/japan/msdn/library/ja/script56/html/wsconwshbasics.asp
- Developing SQL-DMO Applications/http://www.microsoft.com/japan/msdn/library/default.asp?url=/japan/msdn/library/ja/sqldmo/dmoref_con01_2yi7.asp
- http://www.microsoft.com/japan/technet/scriptcenter/scripts/misc/password/mspwvb01.mspx