Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Scripting SQL Server Database Objects Using DMO


By:   |   Read Comments (3)   |   Related Tips: More > Scripts

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(idN'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(idN'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(50NULL 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.


Last Update:






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

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, January 08, 2016 - 10:48:21 AM - Greg Robidoux Back To Top

Hi Imran,

take a look at these other tips:

https://www.mssqltips.com/sqlservertip/3640/script-all-server-level-objects-to-recreate-sql-server/

https://www.mssqltips.com/sqlservertip/1833/generate-scripts-for-database-objects-with-smo-for-sql-server/

https://www.mssqltips.com/sqlservertip/1842/generating-sql-scripts-using-windows-powershell/

Thanks
Greg


Friday, January 08, 2016 - 10:39:40 AM - Imran Back To Top

 Greg,

First of all, Thank you very much ! Its an awesome script you have created. However, it does not work for SQL 2008 and SQL 2012 etc. As you know ,it requires SQLSMO instead of SQLDMO.

Could you please assit me to have this work in SQL 2008 and 2012?

Thanks,

Imran. 

 


Tuesday, October 21, 2008 - 6:28:35 AM - cgreen1 Back To Top

Very nice. 

But be aware that the table scripts do not include triggers or indexes.


Learn more about SQL Server tools