Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 16, 2003

Generate Scripts for SQL Server Objects

By DatabaseJournal.com Staff

by Shailesh Khanal

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

 

ObjectType

ServerName

DBName

ObjectName

TableName

ScriptFile

Database

x

 

x

 

x

Procedure

x

x

x

 

x

View

x

x

x

 

x

Table

x

x

x

 

x

Index

x

x

x

x

x

Trigger

x

x

x

x

x

Key

x

x

x

x

x

Check

x

x

x

x

x

Job

x

 

x

 

x

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


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM