solving sql server problems for millions of dbas and developers since 2006

Scripting SQL Server Database Objects Using DMO

By: Greg Robidoux | Read Comments (1) | Print

Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

Related Tips: More

Problem
One of the nice things you can do with Enterprise Manager is script out all of your objects to a source file. You can then use these files to load your source control, make a backup of your database objects or use the scripts to load another database. The way this scripting is done is by using DMO (Distributed Management Objects). The scripting can be done with many different languages and it gives you control over doing repetitive tasks or making your own interface into SQL Server.

Solution
There is already a lot of great content on the internet and here is one article that already has a process that allows you to script out objects using DMO. While working at a client I had the need to also script out Alerts and Operators, so we could migrate to another server. I tweaked this stored procedure slightly to include the Alerts and Operators. Here is the modified code:

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


SET QUOTED_IDENTIFIER ON  
GO 
SET ANSI_NULLS ON  
GO 

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_ScriptObject]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) 
DROP PROCEDURE [dbo].[sp_ScriptObject] 
GO 

/* This procedure will script out one object so it can be recreated using a T-SQL Script 

Modified 11/18/04 to handle Alerts and Operators 

Usage: 

exec sp_ScriptObject  
       @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' 

*/ 
CREATE PROCEDURE sp_ScriptObject  
       @ServerName VARCHAR(30),  
       @DBName VARCHAR(30),  
       @ObjectName VARCHAR(256),  
       @ObjectType VARCHAR(10),  
       @TableName VARCHAR(50), 
       @ScriptFile VARCHAR(256) 
AS 

DECLARE @CmdStr VARCHAR(256) 
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("' 
    WHEN 'Alert'    THEN 'Jobserver.Alerts("' 
    WHEN 'Operator' THEN 'Jobserver.Operators("' 
  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

To make this process of scripting out Alerts, Jobs and Operators easier, I created a calling stored procedure to loop through all of the objects using a cursor. This could easily be done for any type of object such as tables, procedures, etc...

SET QUOTED_IDENTIFIER OFF  
GO 
SET ANSI_NULLS ON  
GO 

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_ScriptObjects]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) 
DROP PROCEDURE [dbo].[sp_ScriptObjects] 
GO 

/* This procedure will script out all objects of one type so they can be recreated using a T-SQL Script 

Created by Edgewood Solutions 11/18/04 

Usage: 

exec sp_ScriptObjects Alert -- scripts out all alerts 
exec sp_ScriptObjects Job -- scripts out all jobs 
exec sp_ScriptObjects Operator -- scripts out all operators 

The only change needed in the script is the default directory to place these script files @directory 

*/ 
CREATE PROCEDURE sp_ScriptObjects @objecttype VARCHAR(50) = NULL AS 

DECLARE @objectname  sysname, @filename VARCHAR(255), @directory VARCHAR(256) 

SELECT @directory = "D:\BACKUPS\"

IF (@objecttype = 'Alert') 
BEGIN 
DECLARE objects_cursor CURSOR 
   FOR SELECT name FROM msdb.dbo.sysalerts 
END 

IF (@objecttype = 'Operator') 
BEGIN 
DECLARE objects_cursor CURSOR 
   FOR SELECT name FROM msdb.dbo.sysoperators 
END 

IF (@objecttype = 'Job') 
BEGIN 
DECLARE objects_cursor CURSOR 
   FOR SELECT name FROM msdb.dbo.sysjobs 
END 


OPEN objects_cursor 
FETCH NEXT FROM objects_cursor INTO @objectname 

WHILE @@FETCH_STATUS = 0 
BEGIN 

       -- remove junk from name 
       SELECT @filename = REPLACE(@objectname,' ','') 
       SELECT @filename = REPLACE(@filename,':','') 
       SELECT @filename = REPLACE(@filename,'''','') 
       SELECT @filename = REPLACE(@filename,'.','') 
        
       SELECT @filename = @directory + @objecttype + "-"+ @filename + ".sql"

       EXEC master.dbo.sp_ScriptObject  
       @ServerName = @@SERVERNAME,  
       @DBName = '',  
       @ObjectName = @objectname,  
       @ObjectType = @objecttype,  
       @TableName = '', 
       @ScriptFile = @filename 

   FETCH NEXT FROM objects_cursor 
   INTO @objectname 
END 

CLOSE objects_cursor 
DEALLOCATE objects_cursor 
GO 
SET QUOTED_IDENTIFIER OFF  
GO 
SET ANSI_NULLS ON  
GO

Both of these stored procedures should be created in the master database.

To script out all of the Jobs on the server you would issue a command such as the following:

exec sp_ScriptObjects Job

This would then create a separate script file for each Job that exists on the server. The one thing to note is that @directory is hardcoded, so the location of where you want your files to be created should be changed You could also change the sp_ScriptObjects to take directory as a parameter as well as add a process to loop through other objects in your databases.

Next Steps

Take a look at this article on Database Journal where the original script resides
Create these stored procedures on your server to generate scripts
Create a job that runs the process on a periodic basis to script out all of your objects
Tweak the sp_ScriptObjects stored procedure to take other object types such as tables, procedures, etc...
SQL Server 2005 - you need to turn on the "Enable OLE Automation" in the Sufrace Area Configuration Tool to allow these scripts to work.