Generate Scripts for SQL Server Objects

We all like the Generate Script feature of Enterprise Manager and like to use it programmatically. One of the most important uses would be backing up scripts periodically using SQL Server Scheduler.

Enterprise Manager calls SQL Server DMO (Distributed Management Objects) to generate scripts for the objects you select from the GUI. You can write a Visual Basic or a VBScript program to call SQL DMO object's Script method to generate script for the object. Another option, which this article focuses on, is to use Transact SQL to call SQL DMO object using sp_OA* extended stored procedures.

The following stored procedure proc_genscript can be created in any database in your server.

This stored procedure logs into your server using a trusted connection. If you want to use standard login, comment out the line for trusted connection and uncomment the lines for standard connection.

To generate script for an object you have to pass up to six parameters:

exec proc_genscript 
	@ServerName = 'Server Name', 
	@DBName = 'Database Name', 
	@ObjectName = 'Object Name to generate script for', 
	@ObjectType = 'Object Type', 
	@TableName = 'Parent table name for index and trigger',
	@ScriptFile = 'File name to save the script'

Some of the parameters are not required depending on the object type (x indicates required).

The script file location is relative to the SQL Server box.

Usage Example: exec proc_genscript 
	@ServerName = 'MyServer', 
	@DBName = 'Pubs', 
	@ObjectName = 'CK__authors__au_id__77BFCB91', 
	@ObjectType = 'Check', 
	@TableName = 'authors',
	@ScriptFile = 'c:\temp\pubs.sql'

To generate scripts for multiple objects, you can write a procedure, which builds a cursor with all the object names you want scripted and call proc_genscript one by one.

/********************************************************/
/****** Object:  Stored Procedure dbo.proc_genscript    
/******Script Date: 5/8/2003 11:06:52 AM ******/
/****** Created By:	Shailesh Khanal	******/
/********************************************************/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_genscript]') 
  and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_genscript]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.proc_genscript    Script Date: 5/8/2003 11:06:53 AM ******/

CREATE PROCEDURE proc_genscript 
	@ServerName varchar(30), 
	@DBName varchar(30), 
	@ObjectName varchar(50), 
	@ObjectType varchar(10), 
	@TableName varchar(50),
	@ScriptFile varchar(255)
AS

DECLARE @CmdStr varchar(255)
DECLARE @object int
DECLARE @hr int

SET NOCOUNT ON
SET @CmdStr = 'Connect('+@ServerName+')'
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT

--Comment out for standard login
EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE

/* Uncomment for Standard Login
EXEC @hr = sp_OASetProperty @object, 'Login', 'sa'
EXEC @hr = sp_OASetProperty @object, 'password', 'sapassword'
*/

EXEC @hr = sp_OAMethod @object,@CmdStr
SET @CmdStr = 
  CASE @ObjectType
    WHEN 'Database' 	THEN 'Databases("' 
    WHEN 'Procedure'	THEN 'Databases("' + @DBName + '").StoredProcedures("'
    WHEN 'View' 	THEN 'Databases("' + @DBName + '").Views("'
    WHEN 'Table'	THEN 'Databases("' + @DBName + '").Tables("'
    WHEN 'Index'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Indexes("'
    WHEN 'Trigger'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Triggers("'
    WHEN 'Key'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Keys("'
    WHEN 'Check'	THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Checks("'
    WHEN 'Job'	THEN 'Jobserver.Jobs("'
  END

SET @CmdStr = @CmdStr + @ObjectName + '").Script(5,"' + @ScriptFile + '")'
EXEC @hr = sp_OAMethod @object, @CmdStr
EXEC @hr = sp_OADestroy @object
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO