Introduction

I usually come across the need to script out the objects from a SQL Server database to text files. I like to create one script per object with different file extensions to identify the object type. There are many ways this can be done. One can connect to SQL server and script out the objects using Enterprise Manager. One can use Visual Studio and create a database project. This too allows you to script out the object. However, these methods are not effective if you want to automate this process. One should think about a continuous integration process which can allow one to have a clean database build process with the most recent changes. Another benefit is the ability to maintain these scripts files in a source control repository.

Purpose/Scope

The main goal of this article is to show an approach of how to script out the SQL server objects using the SQL-DMO COM object. There are other approaches, but the focus on this article is using this COM object with VB Script. Any other language which supports COM can be used to achieve the same. This script has been tested using SQL 2000/2005 ,MSDE/Express.

Background

This script uses the SQLDMO.SQLServer2 COM interface. DMO stands for Distributed Management Objects. It provides an interface to programmatically manage SQL Server. The reader is encouraged to read further on this topic.

Using the code

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 0g_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

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

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

The code is divided into three main areas. There are the constant declarations which are needed to provide the different scripting options. For example, if one wants the script to first drop the object before creating it. The entry point Main handles the parameter validation and drives the calls to script the objects, and the ScriptObjects subroutine which iterates thru the elements of the collection.

The constant definitions are needed when a call to the Script method is made. There are other options, but these are the ones used in this article.

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.

The Main subroutine reads the arguments. The arguments are referenced by the objArgs variable which holds an array of values. The first three parameters are required. The last two parameters are optional. A reference to the COM interface is created:

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

If only three arguments are passed, the script sets the LoginSecure property to True which indicates that Windows Authentication is used to connect to the database. Otherwise, SQL Authentication is used instead. Once the connection is successful, calls to ScriptObjects are made using the following collections:

• oSql.Databases(database).Tables
• oSql.Databases(database).Views
• oSql.Databases(database).StoredProcedures
• oSql.Databases(database).UserDefinedFunctions

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
  dim user
  dim pw
  Set oSql = WScript.CreateObject("SQLDMO.SQLServer2") 

  if objArgs.Count > 4 then
   user = objArgs(3) 
   pw = objArgs(4) 
   StdOut.Echo "SQL Authentication - Connection to database"
   oSql.LoginSecure = False 'sql authentication 
   oSql.Connect connString ,user,pw 

  else

   StdOut.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: ")
  call ScriptObjects(oSQL.Databases(database).Views,"VIW","Reading views: ") 
  call ScriptObjects(oSQL.Databases(database).StoredProcedures,"PRC","Reading procedures: ") 
  call ScriptObjects(oSQL.Databases(database).UserDefinedFunctions,"UDF","Reading functions: ") 

  oSql.Disconnect 

 else
  StdOut.Echo "Usage: sqlextract.vbs [server or sql instance name] [database] [output folder]" 
 end if
 set oSql = nothing 

 if err.Description <> "" then 
   StdOut.Echo err.Description
 end if
end sub

The ScriptObjects subroutine iterates through the elements in the list. It checks to make sure the object is not a SystemObject. This is because one only needs to script out user objects. The options are a combination of constant values which are passed as an argument to the Script method. This is what allows us to create additional information in the script. This information can be constraint definitions, triggers, options to look if object exists and to drop it before creating it. There are also two additional arguments to this subroutine, ext and msg. The ext argument is used as the file extension. The msg argument is used to display the status of what is being generated. The format of the files generated is OWNER.OBJECTNAME.EXTENSION, so for the MyProc stored procedure which belongs to dbo, the script generated would be:

dbo.MyProc.PRC

sub ScriptObjects(list,ext,msg)
 Dim object
 StdOut.Echo ""
 StdOut.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
 StdOut.Echo object.Name 
 object.Script options, folder + "\" + object.Owner + "." + object.Name + "." + ext 
 End If

 Next

end sub

There are two helper functions to help us create a folder for each object type. The GetFolderName function maps the file extension to a predefined folder name (i.e TAB = Tables). A call to the CreateFolder subroutine is made to create the folder if it does not already exists. The purpose here is to group the object types in separate folders. This is similar to the way the objects are presented using SQL Server tools.

function GetFolderName(ext)
 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
 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

This article only covers a small subset of the functionality available using SQL-DMO. If the goal is to have an automated build and source control process, this article can provide you with some direction. I hope some of you can find this useful.