In the normal development lifecycle, developers design applications one piece at a time. This leads to developers creating tables, views, stored procedures, and other database objects over an extended period of time. Development work might be done on a number of different machines, like the developer’s laptop, or a development server. Eventually, the need will arise to migrate some or all these database objects to another server, like the production server. One of the common ways to migrate these objects is to script out the T-SQL code on the development server, and then run the script on the server where you want the code to be migrated. This article will describe three different ways you can generate T-SQL scripts for Microsoft SQL Server objects.

There are a number of solutions you can used to generate scripts for SQL Server objects. Some of the solutions come with the SQL Server, and others solutions are available through third-party software packages. This article will discuss three methods that are made available when you install the SQL Server:

• Enterprise Manager GUI
• SQLDMO Script Method
• scptxfr.exe

Enterprise Manager GUI

This is the most commonly used method of scripting SQL Server objects. Using the GUI, you can generate a script for a single object or multiple objects with a single execution of the GUI tool. Let’s review how the GUI tool works.

To script an object or set of objects, you must first start the Enterprise manager scripting wizard. This is done by first selecting the database, for which you wish to generate objects, clicking “Tools” from the main menu, then selecting “Generate SQL Scripts…” from the popup Tools window. When you do this, the “Generate SQL Script” screen should pop up. It will look like this:

Figure 1

Note the GUI comes up on the “General” tab. In figure 1, I have chosen to generate objects from the Northwind database on SERVER1. This is not the only way to bring up the scripting wizard. You can also right click on the database, or most objects in the database, select, “All Tasks” from the popup window, and click on “Generate SQL Scripts…”

To select the objects to generate, click on the “Show All” button to display all the objects for which you can generate scripts in the lef-hand side of the above screen shown in figure 1, under the heading, “Objects on Northwind:.” Once there are objects in the left-hand side of the screen, you can select the object or objects for which you wish to generate script, then click “Add>>.” In figure 2, I have selected the “Categories” table, the “Alphabetical list of products” view, and a stored procedure named, “CustOrderHist” as the objects I want to script.

Figure 2

As you can see from figure 2, there are two additional tabs. The “Format” tab can be used to specify how the GUI will script the selected objects, such as the create and drop statements, dependent objects, descriptive headers, and so on. By default, the GUI will generate create and drop statements with a standard format. The “Options” tab allows you to specify the security and table scripting <Greg, is “security and table scripting” one object, or are they 2 objects?> object (e.g., whether you want to script the database and users, and/or index, triggers, and constraints). The “Options” tab also allows you to specify the format for the output file, like OEM, ANSI, or UNICODE, as well as whether you want one script file for all objects scripted, or a separate file for each scripted object. By default, the output is in UNICODE, and a single script file will be created.

Also on the “General” tab, you have a “Preview” button. The preview button allows you to review the script that will be generated when you click the “OK” button on the “General” tab. The “Preview” tab allows you to refine your selection, format, and options to make sure you generate the script for which you are looking. When I click on the “Preview” button in the example screen shown in figure 2, the following preview is displayed:

Figure 3

The “Generate SQL Script Preview” window allows you to review the script from start to finish by using the scroll bar on the side and/or on the bottom. You can copy the generated script into the clipboard, so you can paste it into a text file, query analyzer, or some other place you desire. You can even edit the script you’ve generated by typing directly into the window. Note that if you want to save the edited script, you will need to copy the modified code into the clipboard. The “Close” button takes you back to the “General” tab on the “Generate SQL Scripts” window.

Once you have previewed the code that will be generated and are satisfied, select “OK” on the “Generate SQL Scripts” window. Doing this will generate your script and allow you to save the generated script to a location and name of your choice.

SQL-DMO Script Method

Like Enterprise Manager, the SQL-DMO (SQL Distributed Management Objects) Script method allows you to generate T-SQL code for SQL Server objects, although it does not have a nice GUI to control what to generate. To use the SQL-DMO Script method, you must write your own code to control what is generated. Therefore, this method allows you to build automated processes to script objects, based on your needs and requirements.

The SQL-DMO Script method supports scripting any SQL Server object that can be references as object. This allows you to use the Script method to generate T-SQL for, jobs, triggers, tables, database, views, alerts, and so on.

The following are a few examples of how to generate T-SQL scripts using the SQL-DMO Script method. All these examples will generate T-SQL scripts by executing SQL-DMO using the OLE Automation stored procedures provided with SQL Server. By using this technique, you are using a pure T-SQL solution to generatie SQL Server T-SQL object create scripts. Note that other programming languages such as Visual Basic can also use the SQL-DMO Script method to build automated routines to generate SQL Server objects.

The following is a sample of code that generates the T-SQL code necessary to create the Orders table in database Northwind. The generated T-SQL code is placed into a local variable name @TEMP.

DECLARE @oServer int
DECLARE @method varchar(300)
DECLARE @TSQL varchar(4000)
DECLARE @ScriptType int
EXEC sp_OACreate 'SQLDMO.SQLServer', @oServer OUT
EXEC sp_OASetProperty @oServer, 'loginsecure', 'true'
EXEC sp_OAMethod @oServer, 'Connect', NULL, 'server1'
SET @ScriptType =1|4|32|262144 
SET @method = 'Databases("Northwind").' +
              'Tables("Orders").Script' +
              '(' + CAST (@ScriptType AS CHAR) + ')'
EXEC sp_OAMethod @oServer, @method ,
                           @TSQL OUTPUT
PRINT @TSQL
EXEC sp_OADestroy @oServer

This code creates a server object, connects to the server, generates the T-SQL script using the SQL-DMO Script method, prints the generated T-SQL code, then destroys the server object. The key to what exactly is generated is based on the object on which this method is being applied, and the ScriptType parameter that is associated with the method. In the sample code previously shown, the object that being scripted is Databases(“Northwind”).Tables(“Orders”), and the ScriptType (@ScriptType) values are 1, 4, 32, 262144.

The object being scripted is fairly self-explanatory, but the ScriptType might not be. The ScriptType defines the behavior of the scripting method. By setting different ScriptType values, the Script method will produce different results for the same object. In the previous example, I set four different ScriptType values, 1, 4, 32, and 262144. The following table is from SQL Server books online and describes what will be generated when using each of these ScriptType values.

Constant Value Description

SQLDMOScript_Drops

SQLDMOScript_Default

SQLDMOScript_DatabasePermissions

SQLDMOScript_OwnerQualify

When I run this code on my server and execute the PRINT statement, the following T-SQL code is generated:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, 
N'IsUserTable') = 1)
drop table [dbo].[Orders]
GO
CREATE TABLE [dbo].[Orders] (
        [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
        [CustomerID] [nchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [EmployeeID] [int] NULL ,
        [OrderDate] [datetime] NULL ,
        [RequiredDate] [datetime] NULL ,
        [ShippedDate] [datetime] NULL ,
        [ShipVia] [int] NULL ,
        [Freight] [money] NULL CONSTRAINT [DF_Orders_Freight
DEFAULT (0),
        [ShipName] [nvarchar] (40) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [ShipAddress] [nvarchar] (60) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [ShipCity] [nvarchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [ShipRegion] [nvarchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [ShipPostalCode] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
        [ShipCountry] [nvarchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
      CONSTRAINT [PK_Orders] PRIMARY KEY  CLUSTERED
        (
            [OrderID]
      ) ON   [PRIMARY] ,
      CONSTRAINT [FK_Orders_Customers] FOREIGN KEY 
      (
             [CustomerID]
      ) REFERENCES [dbo].[Customers] (
             [CustomerID]
      ),
         CONSTRAINT [FK_Orders_Employees] FOREIGN KEY 
      (
             [EmployeeID]
      ) REFERENCES [dbo].[Employees] (
             [EmployeeID]
      ),
         CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY 
      (
             [ShipVia]
      ) REFERENCES [dbo].[Shippers] (
             [ShipperID]
      )
) ON [PRIMARY]
GO
GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[Orders] TO 
[public]
GO

This example might be useful in some situations, but I usually want to generate a script and place it in a physical file. This can be accomplished by using additional ScriptType values. There are two different ScriptType values associated with the Script method that are useful for creating physical script files. The following table describes the two additional ScriptType values:

SQLDMOScript_ToFileOnly

SQLDMOScript_AppendToFile

The following code generates T-SQL code for the Orders table and the SalesByCategory stored procedure and outputs the generated code into a physical file called “c:\temp\SQLDMO_Sample_Script.sql.” Note that I added 64 and 256 as additional ScriptType values associated with the execution of the Script method.

DECLARE @oServer int
DECLARE @method varchar(300)
DECLARE @TSQL varchar(4000)
DECLARE @ScriptType int
EXEC sp_OACreate 'SQLDMO.SQLServer', @oServer OUT
EXEC sp_OASetProperty @oServer, 'loginsecure', 'true'
EXEC sp_OAMethod @oServer, 'Connect', NULL, 'server1'
SET @ScriptType =1|4|32|64|256|262144 
SET @method = 'Databases("Northwind").' +
              'Tables("Orders").Script' +
              '(' + CAST (@ScriptType AS CHAR) +
              ',"c:\temp\SQLDMO_Sample_Script.sql")'
EXEC sp_OAMethod @oServer, @method ,
                           @TSQL OUTPUT
SET @method = 'Databases("Northwind").' +
              'StoredProcedures("SalesByCategory").Script' +
              '(' + CAST (@ScriptType AS CHAR) +
              ',"c:\temp\SQLDMO_Sample_Script.sql")'
EXEC sp_OAMethod @oServer, @method ,
                           @TSQL OUTPUT
EXEC sp_OADestroy @oServer

The above code will create the physical file if it does not exist. However, if it does exist, then the code for the two objects will be appended to this physical file. To generate the first object to overwrite an existing file, remove the “256” ScriptType value from the first execution of the Script method. Remember, 256 is the ScriptType value that specifies that the Script method is suppose to append to the output file.

The examples cited in this article so far should help get you get started using T-SQL to call the SQL-DMO Script Method to generate database objects. You can also use the Script method to generate jobs, alerts, and operators. For example, the following generates a script to create an alert:

DECLARE @oServer int
DECLARE @method varchar(300)
DECLARE @TSQL varchar(4000)
DECLARE @ScriptType int
DECLARE @ScriptType2 int
EXEC sp_OACreate 'SQLDMO.SQLServer', @oServer OUT
EXEC sp_OASetProperty @oServer, 'loginsecure', 'true'
EXEC sp_OAMethod @oServer, 'Connect', NULL, 'server1' 
SET @ScriptType =1|4|64
SET @ScriptType2 = 1024|2048
SET @method = 'JobServer.Alerts("Demo: Full msdb log").Script' +
              '(' + cast(@ScriptType as char) +
              ',"C:\temp\sqldmo_alert.sql",' +
              cast(@ScriptType2 as char) + ')'
EXEC sp_OAMethod @oServer, @method , @TSQL OUTPUT
EXEC sp_OADestroy @oServer

As you can see, this script incorporated the ScriptType2 (@ScriptType2) options on the Script Method. These options tell the Script method that the job and notification information should also be scripted. Use SQL Server Books online documentation to find additional information about these two new ScriptType2 options and other Script method options.

scptxfr.exe

The scptxfr.exe executable file comes with your installation of SQL Server. (It can be found in the C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade directory if you use the default location for your install.) This module is normally used to upgrade your server when you install SQL Server, although you can still use it to generate a script or scripts for your database. Since scptxfr.exe was originally intended for upgrading databases to a new version of SQL Server, it does not have the selection of scripting options provided by the Enterprise Manager GUI or the SQL-DMO Script method. Depending on your scripting requirements, this executable may or may not have the scripting capability that you need.

Since scptxfr.exe is just an executable file, it is easy to build some code around it to allow generation of database scripts from T-SQL. This can be done with the following command line syntax:

SCPTXFR /s <server> /d <database> {[/I] | [/P <password>]}
        {[/F <script files directory>] | [/f <single script file>]}
        /q /r /O /T /A /E /C <CodePage> /N /X /H /G /Y /?
/s - Indicates the source server to connect to.
/d - Indicates the source database to script.
/I - Use integrated security.
/P - Password to use for 'sa'. Note that login ID is always 'sa'.
     If /P not used or if a password does not follow the flag,
     a null password is used. Not compatible with /I.
/F - The directory into which the script files should be generated.
     This means one file is generated for each category of objects.
/f - The single file in which all script is to be saved.
     Not compatible with /F.
/q - Use quoted identifiers in the generated scripts.
/r - Include drop statements for the objects in the script.
/O - Generate OEM script files.  Cannot be used with /A or /T.
     This is the default behavior.
/T - Generate UNICODE script files.  Cannot be used with /A or /O.
/A - Generate ANSI script files.  Cannot be used with /T or /O.
/? - Command line help.
/E - Stop scripting when error occurs.
     Default behavior is to log the error, and continue.
/C - Indicate the CodePage which overrides the server CodePage.
/N - Generate ANSI PADDING.
/X - Script SPs and XPs to separate files.
/H - Generate script files without header (default: with header).
/G - Use the specified server name as the prefix for the generated 
     output files(to handle dashes in server name).
/Y - Generate script for Extended Properties (valid for 8.x server
      only).

As you can see there are quite a few options, but no options to select on specific items, such as the Enterprise Manager GUI or SQL-DMO Script method. By using the /F option, you can create a number of script files, one for each category of objects, and the /X option allows you to generate SPs and XPs to a separate file. These might be useful options if you would like to script only the tables for a database, or the SP.

To call scptxfr.exe from T-SQL, you will need to use xp_cmdshell. Here is a sample of what that T-SQL might look like:

declare @cod varchar(1000)
set @cmd = 'master.dbo.xp_cmdshell ' +
           '''c:\"Program Files"\"Microsoft ' +
           'SQL Server"' +
           '\MSSQL\Upgrade\scptxfr.exe ' +
           ' /s server1 /I /d Northwind /f ' +
           'c:\temp\Northwind.sql'''
print @cmd
exec (@cmd)

The above code builds a string, @CMD, that contains the xp_cmdshell call, then executes the string. When this string is executed, the scptxfer.exe will script the database, “Northwind” (/d option) on server “server1” (/s option), using integrated security (/I option), and scripts generated will be placed in a single file called “c:\temp\Northwind.sql” (/f option). The generated script will contain all the objects in the Northwind database.

Conclusion

These three scripting methods should give you the capability to script database objects using either a manual or automated approach. Both SQL-DMO and Enterprise Manager allows you to script particular objects; scptxfer.exe allows you to script an entire database. Depending on your needs, one of these scripting methods should provide you with the script capabilities you require.

--

Gregory A. Larsen is a DBA at Washington State Department of Health. He has been working with computers since the late 1970s, and has a BS in Computer Science, with a minor area of study in Mathematics. During Greg’s DBA career, he has managed databases on Windows, Unix, and IBM mainframes; before becoming a DBA, he developed and maintained applications. Greg has written a number of articles for different web publishing sites, as well as SQL Server Magazine. Greg also maintains a website that contains a number of SQL Server examples to common DBA and SQL developer issues.