sp_generate_script

for RuBoard

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

Listing 21-3 sp_generate_script.

 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='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 discriptive headers for scripts                     [,@server='server name'][, @username='user name'][, @password='password'][, @trustedconnection=1] Returns: (None) $Author: Ken Henderson $. Email: [e-mail] $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='[e-mail]', @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 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 

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.

for RuBoard