ssaari (3/25/2009)

---

Why didn't they bother to include a copy of a working version of the script? I have yet to see a script on this site that works as advertised out of the box.

On Windows Server 2003, SQL2005, I had to put in a call to Main to get Main to run, replace StdOut with WScript.out to get it to list out the objects to the screen. However the subfolders do not get created and the files don't get written, even if I create the subfolders. The GetFolders function never gets called.

Here's what I've got so far. Anybody want to take a crack at finishing it?

'The usage for a SQL 2000/2005/2008 server with SQL Server authentication is:

'cscript 0g_sqlextract.vbs localhost mydatabase c:\sqloutput sa mypassword

'cscript scriptobjs.vbs localhost data_fusion c:\sql_obj_output df_runtime sheckels

'This script connects to a SQL Server database using Windows or SQL Server authentication. Once it is connected, it iterates through the collection of objects in the database and scripts each one of them

'The command line syntax to run the script is as follows:

'cscript sqlextract.vbs [server] [database] [output folder] [username] [password]

' * [server] server location/ip address/instance name

' * [database] database name/initial catalog

' * [output folder] a folder (must exists) where to write the files too

' * [username] user (optional - sql authentication)

' * [password] password (optional)

'The usage for a SQL Express instance with Windows authentication is:

'cscript 0g_sqlextract.vbs localhost\my-sql-instance mydatabase c:\sqloutput

const SQLDMODep_Children = 262144 'List all Microsoft® SQL Server components that depend on the referenced SQL Server component.

const SQLDMOScript_IncludeHeaders = 131072 'Apply descending order to returned list.

const SQLDMOScript_DRI_AllConstraints=520093696 'SQLDMOScript_DRI_Checks, SQLDMOScript_DRI_Defaults, SQLDMOScript_DRI_ForeignKeys, SQLDMOScript_DRI_PrimaryKey, and SQLDMOScript_DRI_UniqueKeys

const SQLDMOScript_ToFileOnly = 64 'output file

const SQLDMOScript_OwnerQualify = 262144 'object owner

const SQLDMOScript_PrimaryObject = 4 'Generate Transact-SQL creating the referenced component.

const SQLDMOScript_ObjectPermissions = 2 'Include Transact-SQL privilege defining statements when scripting database objects.

const SQLDMOScript_IncludeIfNotExists =4096 'if exists

const SQLDMOScript_Indexes = 73736 'indexs

const SQLDMOScript2_NoCollation = 8388608 'no collation

const SQLDMOScript_Triggers = 16 'triggers

const SQLDMOScript_Drops = 1 'Generate Transact-SQL to remove the referenced component. Script tests for existence prior attempt to remove component.

const TAB_FOLDER = "Tables"

const VIW_FOLDER = "Views"

const PROC_FOLDER = "Procedures"

const UDF_FOLDER = "UDFs"

call Main()

'******************************************************************************

sub Main

'******************************************************************************

'get the parameter list

dim objArgs: Set objArgs = WScript.Arguments

if objArgs.Count > 2 then

connString = objArgs(0) 'connection

database = objArgs(1) 'database

folder = objArgs(2) 'output folder

if left(folder,1) <> "\" then folder = folder & "\"

dim user

dim pw

Set oSql = WScript.CreateObject("SQLDMO.SQLServer2")

if objArgs.Count > 4 then

user = objArgs(3)

pw = objArgs(4)

Wscript.Echo "SQL Authentication - Connection to database"

oSql.LoginSecure = False 'sql authentication

oSql.Connect connString ,user,pw

else

Wscript.Echo "windows Authentication - Connection to database"

oSql.LoginSecure = True 'windows authentication

oSql.Connect connString

end if

call ScriptObjects(oSQL.Databases(database).Tables,"TAB","Reading tables: ", folder)

call ScriptObjects(oSQL.Databases(database).Views,"VIW","Reading views: ", folder)

call ScriptObjects(oSQL.Databases(database).StoredProcedures,"PRC","Reading procedures: ", folder)

call ScriptObjects(oSQL.Databases(database).UserDefinedFunctions,"UDF","Reading functions: ", folder)

oSql.Disconnect

else

Wscript.Echo "Usage: sqlextract.vbs [server or sql instance name] [database] [output folder]"

end if

set oSql = nothing

if err.Description <> "" then

Wscript.Echo err.Description

end if

end sub

'******************************************************************************

sub ScriptObjects(list,ext,msg,folder)

'******************************************************************************

Dim object

Wscript.Echo ""

Wscript.Echo msg

'set the scripting options

dim options

options = SQLDMOScript_Indexes _

OR SQLDMOScript_Drops _

OR SQLDMOScript_IncludeIfNotExists _

OR SQLDMOScript_OwnerQualify _

OR SQLDMOScript_PrimaryObject _

Or SQLDMODep_Children _

Or SQLDMOScript_DRI_AllConstraints _

Or SQLDMODep_IncludeHeaders _

Or SQLDMOScript_ObjectPermissions _

Or SQLDMOScript_ToFileOnly _

OR SQLDMOScript2_NoCollation _

OR SQLDMOScript_Triggers

For Each object In list

If Not object.SystemObject Then

Wscript.Echo object.Name

' Something like this??

MyFolder = GetFolderName(ext, folder)

' What does the Script function do?

object.Script options, MyFolder + "\" + object.Owner + "." + object.Name + "." + ext

End If

Next

end sub

'******************************************************************************

function GetFolderName(ext, folder)

'******************************************************************************

dim tmpFolder

tmpFolder = ""

select case ext

case "TAB" tmpFolder = TAB_FOLDER

case "VIW" tmpFolder = VIW_FOLDER

case "PRC" tmpFolder = PROC_FOLDER

case "UDF" tmpFolder = UDF_FOLDER

end select

'WScript.Echo folder + tmpFolder

' I added the "call" and parens

call CreateFolder (folder + tmpFolder)

GetFolderName = folder + tmpFolder

end function

'******************************************************************************

sub CreateFolder(path)

'******************************************************************************

Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")

if not fso.FolderExists(path) then

fso.CreateFolder(path)

end if

set fso=nothing

end sub