I usually come across the need to script out the objects from an 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 the 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 script files in a source control repository.
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 VBScript. Any other language which supports COM can be used to achieve the same. This script has been tested using SQL 2000/2005, MSDE/Express.
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 location/IP address/instance name|
|database name/initial catalog|
|a folder (must exist) where to write the files to|
|user (optional - SQL authentication)|
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 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 through 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
const SQLDMODep_OrderDescending = 131072
const SQLDMOScript_ToFileOnly = 64
const SQLDMOScript_OwnerQualify = 262144
const SQLDMOScript_PrimaryObject = 4
const SQLDMOScript_ObjectPermissions = 2
const SQLDMOScript_IncludeIfNotExists =4096
const SQLDMOScript_Indexes = 73736
const SQLDMOScript2_NoCollation = 8388608
const SQLDMOScript_Triggers = 16
const SQLDMOScript_Drops = 1
const SQLDMOScript_DRI_NonClustered = 4194304
const SQLDMOScript_DRI_Clustered = 8388608
const SQLDMOScript_DRI_Checks = 16777216
const SQLDMOScript_DRI_Defaults = 33554432
const SQLDMOScript_DRI_UniqueKeys = 67108864
const SQLDMOScript_DRI_ForeignKeys = 134217728
const SQLDMOScript_DRI_PrimaryKey = 268435456
const SQLDMOScript_DRI_AllKeys = 469762048
const SQLDMOScript_DRIWithNoCheck = 536870912
const SQLDMOScript_DRI_All = 532676608
const SQLDMOScript_NoIdentity = 1073741824
const PROC_FOLDER = "\stored procedures"
const VIW_FOLDER ="\views"
const UDF_FOLDER = "\functions"
const TAB_FOLDER ="\tables"
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. Once the connection is successful, calls to
ScriptObjects are made using the following collections:
dim objArgs: Set objArgs = WScript.Arguments
if objArgs.Count > 2 then
connString = objArgs(0)
database = objArgs(1)
folder = objArgs(2)
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
oSql.Connect connString ,user,pw
StdOut.Echo "windows Authentication - Connection to database"
oSql.LoginSecure = True
"Reading tables: ")
"Reading views: ")
"PRC","Reading procedures: ")
"UDF","Reading functions: ")
StdOut.Echo "Usage: sqlextract.vbs [server or sql " & _
"instance name] [database] [output folder]"
set oSql = nothing
if err.Description <> "" then
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 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.
options = SQLDMOScript_Indexes _
OR SQLDMOScript_Drops _
OR SQLDMOScript_IncludeIfNotExists _
OR SQLDMOScript_OwnerQualify _
OR SQLDMOScript_PrimaryObject _
Or SQLDMODep_Children _
Or SQLDMOScript_DRI_AllConstraints _
Or SQLDMODep_OrderDescending _
Or SQLDMOScript_ObjectPermissions _
Or SQLDMOScript_ToFileOnly _
OR SQLDMOScript2_NoCollation _
OR SQLDMOScript_Triggers _
For Each object In list
If Not object.SystemObject Then
object.Script options, GetFolderName(ext) + "\" + _
object.Owner + "." + object.Name + "." + ext
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 exist. 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.
tmpFolder = ""
select case ext
case "TAB" tmpFolder = TAB_FOLDER
case "VIW" tmpFolder = VIW_FOLDER
case "PRC" tmpFolder = PROC_FOLDER
case "UDF" tmpFolder = UDF_FOLDER
CreateFolder folder + tmpFolder
GetFolderName = folder + tmpFolder
Set fso = CreateObject("Scripting.FileSystemObject")
if not fso.FolderExists(path) then
Points of Interest
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.
- 0g10112006 - Initial version
- 0g05102008 - Script table keys and create folders for each object type
- 0g02092009 - Source code updated