Sp_generate_script provides a handy utility for scripting out objects from a database. It originally appeared in my last book, The Guru's Guide to Transact-SQL, and has been updated for the latest release of the product and enhanced a bit since then.
You use sp_generate_script by passing it the name of the object you want to script. You can supply a mask with wildcards, if you like, or you can omit the name altogether and the current database will be scripted.
Sp_generate_script uses the SQL-DMO API to do its work. It creates DMO COM objects using sp_OACreate and calls methods on them using sp_OAMethod. Because DMO requires a connection to the server to do any type of scripting, you must pass login information into sp_generate_script so that it can instantiate a new connection to the server using DMO method calls. Once the connection is established, the procedure finds the object or objects for which you want to generate scripts, and adds them to a DMO Transfer object so that they can be written out to a disk file. If you opt to receive a result set (enabled by default), sp_generate_script calls sp_readtextfile to read the script file produced by DMO and returns it as a result set.
The one quirk about the routine is that the DMO Transfer object generates a partial result set that cannot (as far as I can tell) be disabled. So, regardless of whether you ask for a result set, you get a small one after every call to sp_generate_script. I've put a PRINT message in the code just after the call to the Transfer object's ScriptTransfer method telling you to ignore this spurious output, but that's about all I can do. Fortunately, it seems completely harmless: The script is produced despite the message. Here's the code (Listing 21-3):
USE master
GO
IF OBJECT_ID('sp_generate_script') IS NOT NULL
DROP PROC sp_generate_script
GO
CREATE PROC sp_generate_script
@objectname sysname=NULL, --
Object mask to copy
@outputname sysname=NULL, --
Output file to create (default: @objectname+'.SQL')
@scriptoptions int=NULL,
-- Options bitmask for Transfer
@resultset bit=1, -- Determines
whether the script is returned as a result set
@server sysname='(local)',
-- Name of the server to connect to
@username sysname='sa',
-- Name of the user to connect as (defaults to
'sa')
@password sysname=NULL,
-- User's password
@trustedconnection bit=1,
-- Use a trusted connection to connect to the server
@IncludeHeaders bit=1
-- Determines whether descriptive headers are included with
scripts
/*
Object: sp_generate_script
Description: Generates a creation
script for an object or collection of objects
Usage: sp_generate_script
[@objectname='Object name or mask (defaults to all object in
current database)']
[,@outputname='Outpu
t file name' (Default: @objectname+'.SQL', or
GENERATED_SCRIPT.
SQL for entire database)]
[,@scriptoptions=bitmask
specifying script generation options]
[,@resultset=bit specifying
whether to generate a result set
[,@includeheaders=bit
specifying whether to generate discriptive
headers for scripts
[,@server='server name'][,
@username='user name'][,
@password='password'][,
@trustedconnection=1]
Returns: (None)
$Author: Ken Henderson $.
Email: khen@khen.com
$Revision: 8.0 $
Example: sp_generate
_script @objectname='authors', @outputname='authors.sql'
Created: 1998-04-01.
$Modtime: 2000-12-16 $.
*/
AS
-- SQLDMO_SCRIPT_TYPE vars
DECLARE @SQLDMOScript_Default int
DECLARE @SQLDMOScript_Drops int
DECLARE @SQLDMOScript_ObjectPermissions int
DECLARE @SQLDMOScript_PrimaryObject int
DECLARE @SQLDMOScript_ClusteredIndexes int
DECLARE @SQLDMOScript_Triggers int
DECLARE @SQLDMOScript_DatabasePermissions int
DECLARE @SQLDMOScript_Permissions int
DECLARE @SQLDMOScript_ToFileOnly int
DECLARE @SQLDMOScript_Bindings int
DECLARE @SQLDMOScript_AppendToFile int
DECLARE @SQLDMOScript_NoDRI int
DECLARE @SQLDMOScript_UDDTsToBaseType int
DECLARE @SQLDMOScript_IncludeIfNotExists int
DECLARE @SQLDMOScript_NonClusteredIndexes int
DECLARE @SQLDMOScript_Indexes int
DECLARE @SQLDMOScript_Aliases int
DECLARE @SQLDMOScript_NoCommandTerm int
DECLARE @SQLDMOScript_DRIIndexes int
DECLARE @SQLDMOScript_IncludeHeaders int
DECLARE @SQLDMOScript_OwnerQualify int
DECLARE @SQLDMOScript_TimestampToBinary int
DECLARE @SQLDMOScript_SortedData int
DECLARE @SQLDMOScript_SortedDataReorg int
DECLARE @SQLDMOScript_TransferDefault int
DECLARE @SQLDMOScript_DRI_NonClustered int
DECLARE @SQLDMOScript_DRI_Clustered int
DECLARE @SQLDMOScript_DRI_Checks int
DECLARE @SQLDMOScript_DRI_Defaults int
DECLARE @SQLDMOScript_DRI_UniqueKeys int
DECLARE @SQLDMOScript_DRI_ForeignKeys int
DECLARE @SQLDMOScript_DRI_PrimaryKey int
DECLARE @SQLDMOScript_DRI_AllKeys int
DECLARE @SQLDMOScript_DRI_AllConstraints int
DECLARE @SQLDMOScript_DRI_All int
DECLARE @SQLDMOScript_DRIWithNoCheck int
DECLARE @SQLDMOScript_NoIdentity int
DECLARE @SQLDMOScript_UseQuotedIdentifiers int
-- SQLDMO_SCRIPT2_TYPE vars
DECLARE @SQLDMOScript2_Default int
DECLARE @SQLDMOScript2_AnsiPadding int
DECLARE @SQLDMOScript2_AnsiFile int
DECLARE @SQLDMOScript2_UnicodeFile int
DECLARE @SQLDMOScript2_NonStop int
DECLARE @SQLDMOScript2_NoFG int
DECLARE @SQLDMOScript2_MarkTriggers int
DECLARE @SQLDMOScript2_OnlyUserTriggers int
DECLARE @SQLDMOScript2_EncryptPWD int
DECLARE @SQLDMOScript2_SeparateXPs int
-- SQLDMO_SCRIPT_TYPE values
SET @SQLDMOScript_Default = 4
SET @SQLDMOScript_Drops = 1
SET @SQLDMOScript_ObjectPermissions = 2
SET @SQLDMOScript_PrimaryObject = 4
SET @SQLDMOScript_ClusteredIndexes = 8
SET @SQLDMOScript_Triggers = 16
SET @SQLDMOScript_DatabasePermissions = 32
SET @SQLDMOScript_Permissions = 34
SET @SQLDMOScript_ToFileOnly = 64
SET @SQLDMOScript_Bindings = 128
SET @SQLDMOScript_AppendToFile = 256
SET @SQLDMOScript_NoDRI = 512
SET @SQLDMOScript_UDDTsToBaseType = 1024
SET @SQLDMOScript_IncludeIfNotExists = 4096
SET @SQLDMOScript_NonClusteredIndexes = 8192
SET @SQLDMOScript_Indexes = 73736
SET @SQLDMOScript_Aliases = 16384
SET @SQLDMOScript_NoCommandTerm = 32768
SET @SQLDMOScript_DRIIndexes = 65536
SET @SQLDMOScript_IncludeHeaders = 131072
SET @SQLDMOScript_OwnerQualify = 262144
SET @SQLDMOScript_TimestampToBinary = 524288
SET @SQLDMOScript_SortedData = 1048576
SET @SQLDMOScript_SortedDataReorg = 2097152
SET @SQLDMOScript_TransferDefault = 422143
SET @SQLDMOScript_DRI_NonClustered = 4194304
SET @SQLDMOScript_DRI_Clustered = 8388608
SET @SQLDMOScript_DRI_Checks = 16777216
SET @SQLDMOScript_DRI_Defaults = 33554432
SET @SQLDMOScript_DRI_UniqueKeys = 67108864
SET @SQLDMOScript_DRI_ForeignKeys = 134217728
SET @SQLDMOScript_DRI_PrimaryKey = 268435456
SET @SQLDMOScript_DRI_AllKeys = 469762048
SET @SQLDMOScript_DRI_AllConstraints = 520093696
SET @SQLDMOScript_DRI_All = 532676608
SET @SQLDMOScript_DRIWithNoCheck = 536870912
SET @SQLDMOScript_NoIdentity = 1073741824
SET @SQLDMOScript_UseQuotedIdentifiers = -1
-- SQLDMO_SCRIPT2_TYPE values
SET @SQLDMOScript2_Default = 0
SET @SQLDMOScript2_AnsiPadding = 1
SET @SQLDMOScript2_AnsiFile = 2
SET @SQLDMOScript2_UnicodeFile = 4
SET @SQLDMOScript2_NonStop = 8
SET @SQLDMOScript2_NoFG = 16
SET @SQLDMOScript2_MarkTriggers = 32
SET @SQLDMOScript2_OnlyUserTriggers = 64
SET @SQLDMOScript2_EncryptPWD = 128
SET @SQLDMOScript2_SeparateXPs = 256
DECLARE @dbname sysname,
@srvobject int, -- SQL Server object
@object int, -- Work variable for accessing COM objects
@hr int, -- Contains HRESULT returned by COM
@tfobject int, -- Stores pointer to Transfer object
@res int
SET @res=0
IF (@objectname='/?') GOTO Help
IF (@objectname IS NOT NULL) AND
(CHARINDEX('%',@objectname)=0) AND
(CHARINDEX('_',@objectname)=0) BEGIN
SET @dbname=ISNULL(PARSENAME(@objectname,3)
,DB_NAME()) -- Extract the DB name; default
to current
SET @objectname=PARSENAME(@objectname,1)
-- Remove extraneous stuff from table
name
IF (@objectname IS NULL) BEGIN
RAISERROR('Invalid object name.',16,1)
RETURN -1
END
IF (@outputname IS NULL)
SET @outputname=@objectname+'.SQL'
END ELSE BEGIN
SET @dbname=DB_NAME()
IF (@outputname IS NULL)
SET @outputname='GENERATED_SCRIPT.SQL'
END
-- Create a SQLServer object
EXEC @hr=sp_OACreate 'SQLDMO.SQLServer', @srvobject OUTPUT
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @srvobject, @hr
RETURN
END
-- Create a Transfer object
EXEC @hr=sp_OACreate 'SQLDMO.Transfer', @tfobject OUTPUT
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
-- Set Transfer's CopyData property
EXEC @hr = sp_OASetProperty @tfobject, 'CopyData', 0
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
-- Tell Transfer to copy the schema
EXEC @hr = sp_OASetProperty @tfobject, 'CopySchema', 1
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
IF (@objectname IS NULL) BEGIN --
Get all objects in the database
-- Tell Transfer to copy all objects
EXEC @hr = sp_OASetProperty @tfobject, 'CopyAllObjects', 1
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
-- Tell Transfer to get groups as well
EXEC @hr = sp_OASetProperty @tfobject, 'IncludeGroups', 1
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
-- Tell it to include users
EXEC @hr = sp_OASetProperty @tfobject, 'IncludeUsers', 1
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
-- Tell it to include logins
EXEC @hr = sp_OASetProperty @tfobject, 'IncludeLogins', 1
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
-- Include object dependencies too
EXEC @hr = sp_OASetProperty @tfobject, 'IncludeDependencies', 1
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
IF (@scriptoptions IS NULL) BEGIN
SET @scriptoptions=@SQLDMOScript
_OwnerQualify | @SQLDMOScript_Default |
@SQLDMOScript_Triggers |
@SQLDMOScript_Bindings | @SQLDMOScript_DatabasePermissions |
@SQLDMOScript_Permissions | @SQLDMOScript_ObjectPermissions |
@SQLDMOScript_ClusteredIndexes | @SQLDMOScript_Indexes |
@SQLDMOScript_Aliases |
@SQLDMOScript_DRI_All
IF @includeheaders=1 SET @scriptoptions=@scriptoptions |
@SQLDMOScript_IncludeHeaders
END
END ELSE BEGIN
DECLARE @obname sysname,
@obtype varchar(2),
@obowner sysname,
@OBJECT_TYPES varchar(50),
@obcode int
-- Used to translate sysobjects.type into the
bitmap that Transfer requires
SET @OBJECT_TYPES='T V U P
D R TR FN TF IF '
-- Find all the objects that match the
supplied mask and add them to Transfer's
-- list of objects to script
DECLARE ObjectList CURSOR FOR
SELECT name,CASE type WHEN 'TF' THEN
'FN' WHEN 'IF' THEN 'FN' ELSE type END AS
type,USER_NAME(uid) FROM sysobjects
WHERE (name LIKE @objectname)
AND (CHARINDEX(type+' ',@OBJECT_TYPES)<>0)
AND (OBJECTPROPERTY(id,'IsSystemTable')=0)
AND (status>0)
UNION ALL -- Include user-defined data types
SELECT name,'T',USER_NAME(uid)
FROM SYSTYPES
WHERE (usertype & 256)<>0
AND (name LIKE @objectname)
OPEN ObjectList
FETCH ObjectList INTO @obname, @obtype, @obowner
WHILE (@@FETCH_STATUS=0) BEGIN
SET @obcode=POWER(2,(CHARINDEX
(@obtype+' ',@OBJECT_TYPES)/3))
EXEC @hr = sp_OAMethod @tfobject,
'AddObjectByName', NULL, @obname, @obcode, @obowner
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
FETCH ObjectList INTO @obname, @obtype, @obowner
END
CLOSE ObjectList
DEALLOCATE ObjectList
IF (@scriptoptions IS NULL)
SET @scriptoptions=@SQLDMOScript_
Default -- Keep it simple when not scripting the
entire database
END
-- Set Transfer's ScriptType property
EXEC @hr = sp_OASetProperty
@tfobject, 'ScriptType', @scriptoptions
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
RETURN
END
-- Connect to the server
IF (@trustedconnection=1) BEGIN
EXEC @hr = sp_OASetProperty @srvobject, 'LoginSecure', 1
IF (@hr <> 0) GOTO ServerError
EXEC @hr = sp_OAMethod @srvobject,
'Connect', NULL, @server
END ELSE BEGIN
IF (@password IS NOT NULL)
EXEC @hr = sp_OAMethod @srvobject,
'Connect', NULL, @server, @username,
@password
ELSE
EXEC @hr = sp_OAMethod @srvobject,
'Connect', NULL, @server, @username
END
IF (@hr <> 0) GOTO ServerError
-- Get a pointer to the SQLServer object's Databases collection
EXEC @hr = sp_OAGetProperty
@srvobject, 'Databases', @object OUT
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo
@srvobject, @hr
RETURN
END
-- Get a pointer from the Databases
collection for the specified database
EXEC @hr = sp_OAMethod
@object, 'Item', @object OUT, @dbname
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Call the Database object's ScriptTransfer
method to create the script
EXEC @hr = sp_OAMethod
@object, 'ScriptTransfer',NULL, @tfobject, 2, @outputname
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
PRINT 'NOTE: Ignore the code displayed
above. It''s a remnant of the SQL-DMO method used
to produce
the script file'
IF (@resultset=1) EXEC sp_readtextfile @outputname
GOTO ExitPoint
ServerError:
SET @res=-2
ExitPoint:
EXEC sp_OADestroy @srvobject
-- For cleanliness
EXEC sp_OADestroy @tfobject
-- For cleanliness
RETURN @res
Help:
EXEC sp_usage @objectname='sp_generate_script',
@desc='Generates a creation script for
an object or collection of objects',
@parameters='[@objectname=''Object name or mask (defaults to all
object in current database)'']
[,@outputname=''Output file name'' (Default: @objectname+''.SQL'', or
GENERATED_SCRIPT.SQL for entire database)]
[,@scriptoptions=bitmask specifying script generation options]
[,@resultset=bit specifying whether to generate a result set
[,@includeheaders=bit specifying whether to generate descriptive
headers for scripts
[,@server=''server name''][, @username=''user name''][,
@password=''password''][, @trustedconnection=1]',
@author='Ken Henderson', @email='khen@khen.com',
@version='8', @revision='0',
@datecreated='19980401', @datelastchanged='20001216',
@example='sp_generate_script
@objectname=''authors'', @outputname=''authors.sql'' '
RETURN -1
GO
USE Northwind
GO
EXEC sp_generate_script 'Customers', @server='khenmp\ss2000'
(Results)
Column1 ------------------------------------------ set quoted_identifier OFF GO CREATE TABLE [Customers] ( [CustomerID] [nchar] (5) COLLATE SQL_Latin1_ General_CP1_CI_AS NOT NULL , [CompanyName] [nvarchar] (40) COLLATE SQL _Latin1_General_CP1_CI_AS NOT NULL , [ContactName] [nvarchar] (30) COLLATE SQL _Latin1_General_CP1_CI_AS NULL , [ContactTitle] [nvarchar] (30) COLLATE SQL_ Latin1_General_CP1_CI_AS NULL , [Address] [nvarchar] (60) COLLATE SQL_ Latin1_General_CP1_CI_AS NULL , [City] [nvarchar] (15) COLLATE SQL_Latin1_ General_CP1_CI_AS NULL , [Region] [nvarchar] (15) COLLATE SQL_ Latin1_General_CP1_CI_AS NULL , [PostalCode] [nvarchar] (10) COLLATE SQL_ Latin1_General_CP1_CI_AS NULL , [Country] [nvarchar] (15) COLLATE SQL _Latin1_General_CP1_CI_AS NULL , [Phone] [nvarchar] (24) COLLATE SQL_ Latin1_General_CP1_CI_AS NULL , [Fax] [nvarchar] (24) COLLATE SQL_ Latin1_General_CP1_CI_AS NULL , [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF__Customers__rowgu__ 0EF836A4] DEFAULT (newid()), CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ( [CustomerID] ) ON [PRIMARY] ) ON [PRIMARY] GO (1 row(s) affected) NOTE: Ignore the code displayed above. It's a remnant of the SQL-DMO method used to produce the script file line ---------------------------------------- set quoted_identifier OFF GO CREATE TABLE [Customers] ( [CustomerID] [nchar] (5) COLLATE SQL_Latin1 _General_CP1_CI_AS NOT NULL , [CompanyName] [nvarchar] (40) COLLATE SQL _Latin1_General_CP1_CI_AS NOT NULL , [ContactName] [nvarchar] (30) COLLATE SQ L_Latin1_General_CP1_CI_AS NULL , [ContactTitle] [nvarchar] (30) COLLATE SQ L_Latin1_General_CP1_CI_AS NULL , [Address] [nvarchar] (60) COLLATE SQL _Latin1_General_CP1_CI_AS NULL , [City] [nvarchar] (15) COLLATE SQL _Latin1_General_CP1_CI_AS NULL , [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF__Customers__rowgu __0EF836A4] DEFAULT (newid()), CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ( [CustomerID] ) ON [PRIMARY] ) ON [PRIMARY] GO
Everything above the PRINT message (in bold type) is spurious output that you can safely ignore. The output below the message is the actual script. Here I've scripted the Customers table from the Northwind database. I could just as easily have scripted the entire database, or supplied a mask to generate a script of several objects at once.
The procedure begins by instantiating the DMO SQLServer and Transfer objects. DMO's SQLServer object is its root-level access path; you use it to connect to the server and to access other objects on the server. The Transfer object encapsulates DMOs server-to-server or server-to-file object and data transfer facility. Sp_generate_script uses it to generate SQL scripts.
If you've done any DMO programming, you may be wondering why I'm using a Transfer object instead of calling the Script method on individual objects. The reason for this is simple: object dependencies. The Transfer object writes object schema info to the script in order of dependency. Because it was originally intended to support transferring one database to another, it has to be mindful of object creation order; otherwise, CREATE statements for objects that depended on other objects will fail if the objects they require haven't yet been created. Consider a foreign key constraint. If the Order Details table makes a foreign key reference to the Products table, the Products table must exist before the Order Details table can be created. The CREATE TABLE statement will fail if it doesn't. The Transfer object ensures this by checking object dependencies when it scripts out a database.
Once the Transfer object is created, the procedure determines whether the user wants to script the entire database or only selected objects. This distinction is important because DMO lists objects in order of dependency when scripting an entire database, as I've said. If only a subset of the objects in a database is to be scripted, the procedure opens a cursor on the sysobjects and systypes tables (via UNION ALL) and calls Transfer's AddObjectByName method to set them up for scripting, one by one.
The procedure next uses the SQLServer object to locate the database housing the objects it needs to script. It finds this database by accessing the object's Databases collection. DMO objects often expose collections of other objects. Items in these collections can be accessed by name or by ordinal index. In the case of sp_generate_script, collection items are always accessed by name.
Once the procedure retrieves a pointer to the correct database, it calls that database's ScriptTransfer method, passing it the previously created Transfer object as a parameter. This generates a SQL script containing the objects we've specified.
The final step in the procedure is to return the script as a result set. Usually, the caller will expect to see the script immediately. If @resultset = 1 (the default), sp_generate_script calls sp_readtextfile to return the newly generated script file to the caller via a result set. A useful variation of this would be to return a cursor pointer to the script, but that's an exercise best left to the reader.