Click here to Skip to main content
15,868,056 members
Articles / Database Development / SQL Server

How to Script Out SQL Server Objects

Rate me:
Please Sign up or sign in to vote.
4.76/5 (18 votes)
10 Feb 2009CPOL4 min read 145K   1K   47   27
A VBScript script which allows to script out all the SQL Server objects in each independent file

Introduction

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.

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 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.

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 exist) where to write the files to

[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 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.

VBScript
'List all Microsoft® SQL Server™ components that 
'depend on the referenced SQL Server component.
const SQLDMODep_Children = 262144
'Apply descending order to returned list.
const SQLDMODep_OrderDescending = 131072
'SQLDMOScript_DRI_Checks, SQLDMOScript_DRI_Defaults, 
'SQLDMOScript_DRI_ForeignKeys, SQLDMOScript_DRI_PrimaryKey, and 
'SQLDMOScript_DRI_UniqueKeys
const SQLDMOScript_DRI_AllConstraints=520093696 
const SQLDMOScript_ToFileOnly = 64              'output file
const SQLDMOScript_OwnerQualify =  262144       'object owner
'Generate Transact-SQL creating the referenced component.
const SQLDMOScript_PrimaryObject = 4
'Include Transact-SQL privilege defining statements 
'when scripting database objects.
const SQLDMOScript_ObjectPermissions = 2
const SQLDMOScript_IncludeIfNotExists =4096     'if exists
const SQLDMOScript_Indexes = 73736              'indexes
const SQLDMOScript2_NoCollation = 8388608       'no collation
const SQLDMOScript_Triggers = 16                'triggers
'Generate Transact-SQL to remove the referenced component. 
'Script tests for existence prior attempt to remove component.
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

'folder definitions
const PROC_FOLDER = "\stored procedures"
const VIW_FOLDER ="\views"
const UDF_FOLDER = "\functions"
const TAB_FOLDER ="\tables"

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:

VBScript
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:

  • oSql.Databases(database).Tables
  • oSql.Databases(database).Views
  • oSql.Databases(database).StoredProcedures
  • oSql.Databases(database).UserDefinedFunctions
VBScript
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.

VBScript
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_OrderDescending _
            Or SQLDMOScript_ObjectPermissions _
            Or SQLDMOScript_ToFileOnly _
            OR SQLDMOScript2_NoCollation _
            OR SQLDMOScript_Triggers _
        OR SQLDMOScript_DRI_All
    
    For Each object In list
    
        If Not object.SystemObject Then
            StdOut.Echo object.Name
            object.Script options, GetFolderName(ext) + "\" + _
                          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 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.

VBScript
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

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.

History

  • 0g10112006 - Initial version
  • 0g05102008 - Script table keys and create folders for each object type
  • 0g02092009 - Source code updated

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect OG-BITechnologies
United States United States
Software engineer, author & speaker who enjoys mentoring, learning, speaking and sharing with others about software development technologies. Microsoft MVP.

My Blog

Comments and Discussions

 
GeneralMy vote of 5 Pin
Orlando Selenu15-Mar-12 23:41
Orlando Selenu15-Mar-12 23:41 
GeneralRe: My vote of 5 Pin
ozkary18-Sep-12 4:49
ozkary18-Sep-12 4:49 
Questionerror when trying to run the script Pin
onmni20-Aug-11 21:25
onmni20-Aug-11 21:25 
AnswerRe: error when trying to run the script Pin
Lonyem23-Sep-11 7:58
Lonyem23-Sep-11 7:58 
GeneralMy vote of 5 Pin
prmioto23-Feb-11 3:42
prmioto23-Feb-11 3:42 
QuestionHow would you script Triggers? Pin
wooddsw18-Nov-09 2:25
wooddsw18-Nov-09 2:25 
AnswerRe: How would you script Triggers? Pin
ozkary18-Nov-09 4:03
ozkary18-Nov-09 4:03 
GeneralRe: How would you script Triggers? Pin
wooddsw18-Nov-09 4:28
wooddsw18-Nov-09 4:28 
QuestionHave you found a solution to avoid the varchar(-1) generated Pin
singueur19-Aug-09 11:00
singueur19-Aug-09 11:00 
QuestionRe: Have you found a solution to avoid the varchar(-1) generated Pin
uuthanh30-Sep-09 5:50
uuthanh30-Sep-09 5:50 
AnswerRe: Have you found a solution to avoid the varchar(-1) generated Pin
ozkary30-Sep-09 7:54
ozkary30-Sep-09 7:54 
GeneralRe: Have you found a solution to avoid the varchar(-1) generated Pin
uuthanh30-Sep-09 17:09
uuthanh30-Sep-09 17:09 
Answergreat tool ... Pin
Coosile216-Jul-09 12:28
Coosile216-Jul-09 12:28 
Hi Ozkar,

very good job. That is something you don't want to miss once you saw it. I did some minor improvements.
I very often just add my content to existing DBs. I use a naming scheme that identifies all my USPs, tables and views. And I only want to dump those objects afterwards.
So I added a filter argument. If empty ("") the script dumps everything, if a literal is given it only dumps objects with that string inside the name.

It also creates a new folder everytime you run it. It creates a folder for the current date and a subfolder with the current time. So you get a kind of versioning.

Thanks again.

' Author: ogarcia (og-bit.com)  ; improved by coosile (2009-07-15)
' Date :   10/11/2006
' Usage: cscript dump-sql-objects.vbs [server or sql instance name] [database] [output folder] [pattern]
'
'

Option Explicit

Dim StdIn:  Set StdIn = WScript.StdIn
Dim StdOut: Set StdOut = WScript
dim connString
dim database
dim folder
dim oSql: 
dim pattern

const SQLDMODep_Children = 262144               'List all Microsoft® SQL Server™ components that depend on the referenced SQL Server component.
const SQLDMODep_IncludeHeader = 131072        'add a datetime header
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 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"
Dim dateStr : dateStr = getDateString
Dim timeStr : timeStr = getTimeString
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      
      pattern = objArgs(3)		'pattern to search for in obj
      
	  logit "Connect to server [" & connString & "] and DB [" & database & "] and store objects in [" & folder & "] and use pattern [" & pattern & "]"
      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


'GetFolderName
'
'resolves the folder name based on the file ext
'
' ext  - extension name
'
function GetFolderName(ext)
	Dim newFolder
   
   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

	newFolder = folder + "\" + dateStr & "\" & timeStr & "\" & database + tmpFolder
    CreateFolder newFolder
    GetFolderName = newFolder

end function

'CreateFolder
'
'creates a folder to write files to
'
'path the path to the new folder
sub CreateFolder(path) 
	Dim fso
	Set fso = CreateObject("Scripting.FileSystemObject")
	
	if not fso.FolderExists(fso.GetParentFolderName(path)) Then
		createFolder fso.GetParentFolderName(path)
	End IF
	if not fso.FolderExists(path) then
		fso.CreateFolder(path)
	end if

	set fso=nothing

end sub

'ScriptObjects
'
'scrips all the objects
'
' list   - collection
' ext    - extension name to use
' msg    - message to display
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_IncludeHeader _
			Or SQLDMOScript_ObjectPermissions _
			Or SQLDMOScript_ToFileOnly _
			OR SQLDMOScript2_NoCollation _
			OR SQLDMOScript_Triggers _
			OR SQLDMOScript_DRI_All
    
    For Each object In list
    
		If Not object.SystemObject Then
			If InStr(object.Name, pattern) OR pattern = "" Then
				StdOut.Echo object.Name			                                                
				object.Script options, GetFolderName(ext)   + "\" + object.Owner + "." + object.Name + "." + ext  	                                                          
			End If
		End If
    
    Next
    
end sub

'**************************************
'gets current date and time in a sorable 
'format
Function getDateString()
	Dim dtDay
	Dim dtMonth
	Dim dtYear
	Dim dt
	
	dt = Date
	dtDay = formatDT(Day(dt))
	dtMonth = formatDT(Month(dt))
	dtYear = formatDT(Year(dt))
	
	getDateString = dtYear & "-" & dtMonth & "-" & dtDay
End Function


'**************************************
'gets current date and time in a sorable 
'format
Function getTimeString()
	Dim dt
	Dim dtH, dtM, dtS
	
	dt = Time
	dtH = formatDT(Hour(dt))
	dtM = formatDT(Minute(dt))
	dtS = formatDT(Second(dt))

	getTimeString = dtH & "-" & dtM & "-" & dtS
End Function

'**************************************
'handles single character date parts
Function formatDT( dtpart )
	If dtpart < 10 Then 
		formatDT = "0" & CStr(dtpart)
	Else
		formatDT = CStr(dtpart)
	End If
End Function


Sub logit(text)
	Wscript.echo text
End Sub

QuestionANy tips for getting this to work with SQL SERVER 2005 named instances? Pin
tradergordo6-May-09 11:18
tradergordo6-May-09 11:18 
QuestionOrderDescending Option Wrong? Pin
Randy Syring27-Jan-09 11:32
Randy Syring27-Jan-09 11:32 
AnswerRe: OrderDescending Option Wrong? Pin
ozkary9-Feb-09 4:45
ozkary9-Feb-09 4:45 
Generalgood starting point, but needed work Pin
frendy glasser25-Sep-08 2:02
frendy glasser25-Sep-08 2:02 
General[Message Removed] Pin
Mojtaba Vali23-May-08 22:07
Mojtaba Vali23-May-08 22:07 
GeneralGreat script! Pin
chrelad9-May-08 8:07
chrelad9-May-08 8:07 
GeneralRe: Great script! Pin
ozkary11-May-08 3:48
ozkary11-May-08 3:48 
GeneralHere is another snippit that scripts out constraints (for those who care) Pin
Punktech.com1-May-08 11:20
Punktech.com1-May-08 11:20 
QuestionRe: Here is another snippit that scripts out constraints (for those who care) Pin
chrelad9-May-08 7:52
chrelad9-May-08 7:52 
NewsRe: Here is another snippit that scripts out constraints (for those who care) Pin
chrelad9-May-08 8:06
chrelad9-May-08 8:06 
GeneralRe: Here is another snippit that scripts out constraints (for those who care) Pin
ozkary11-May-08 4:38
ozkary11-May-08 4:38 
GeneralAwesome script! Pin
maniatisa23-Apr-07 9:50
maniatisa23-Apr-07 9:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.