At work we need to script out all the objects in a database on a regular basis. I wanted to do this automatically and have found DMO to be the solution.
The website linked to above comes with a useful scipt to do exactly what I wanted, which I have listed below in its entirety.
The author of the post recommends using DMO for anything that you find you are scripting on a regular basis. Its definitely proved useful for this job so will be looking to use it for other db admin stuff in the future.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.spScriptObjects Script Date: 23/11/2006 17:25:13 ******/
/****** Object: Stored Procedure dbo.spScriptObject Script Date: 07/11/2006 17:46:05 ******/
ALTER procedure spScriptObjects
/*scripts out one or more of the databases on the specified server in a number of ways
using the DMO to do so */
@SourceServer varchar(128) = 'MyServer' ,
@DatabaseWildcard varchar(128) = 'MyDatabase',
@SourceUID varchar(128) = null,
@SourcePWD varchar(128) = null,
@WorkDir varchar(500) = 'C:\temp',
@Workfile varchar(500) = null,
@ScriptFileMode int = 8--1 2 4 or 8 see below for the key
as
/*
If you are using windows authentication then use DEFAULT instead of
the @SourceUID and @SourcePWD. If calling by parameter, then just
leave them out!
--write out the PUBS database to one file organised by object type
--and named MyServer_PUBS.SQL
spScriptObjects 'MyServer', 'PUBS', 'sa', 'secret', 'C:\database'
--write out the PUBS database to one file organised by object type
--and named MyNiceFilename.txt
spScriptObjects 'MyServer', 'PUBS', 'sa', 'secret', 'C:\database',
'MyNiceFilename.txt'
--write out all the databases on the server 'MYSERVER, each one in
a single file, organised by object type
spScriptObjects 'MyServer', '%', 'sa', 'secret', 'C:\database'
--write out all the databases on the server 'MYSERVER that begin with
the letter M, each one in a single file, organised by object type
spScriptObjects 'MyServer', 'M%', 'sa', 'secret', 'C:\database'
--write out the PUBS database to several files in a PUBS subdirectory
spScriptObjects 'BABBAGE', 'PUBS', 'sa','secret', 'C:\database','',4
--write out all database to several files in their own subdirectories
spScriptObjects 'BABBAGE', '%', 'sa', 'secret', 'C:\database','',4
--write out the PUBS database to several files in a PUBS subdirectory
--one file for tables, amother for triggers and so on
spScriptObjects 'BABBAGE', 'PUBS', 'sa','thumper', 'E:\database','',1
@ScriptFileMode is as follows:
1 Command batch is written to multiple files, one file for each kind of
object transferred. For example, generate a file for user-defined data
types and a separate file for tables. Specify a path using the ScriptFile
argument.
2 Command batch is written to one file. Specify the file name using the
ScriptFile argument.
4 Command batch is written to multiple files, one file for each
SQL Server component transferred. Specify a path using the ScriptFile
argument.
8 Command batch is written to one file. Command batch contents are organized
by object type. Specify the file name using the ScriptFile argument.
*/
declare
@hr int ,
@strErrorMessage Varchar(255) ,
@objErrorObject int ,
@objServer int ,
@objTransfer int ,
@strFilename varchar(255) ,
@strResult varchar(255) ,
@strCommand varchar(255) ,
@objDatabases int,
@objSpecificDB int,
@ii int,
@iiMax int,
@Command varchar(255),
@SourceDB varchar(255)
Set nocount on
create table #junk (line ntext)
SELECT @strErrorMessage = 'instantiating the DMO',@objErrorObject=@objTransfer
exec @hr= sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
if @SourcePWD is null or @SourceUID is null
begin
--use a trusted connection
if @hr=0 Select @strErrorMessage='Setting login to windows authentication on '+@SourceServer, @objErrorObject=@objServer
if @hr=0 exec @hr = sp_OASetProperty @objServer, 'LoginSecure', 1
if @hr=0 Select @strErrorMessage='logging in to the requested server using windows authentication on '+@SourceServer
if @SourceUID is null and @hr=0 exec @hr = sp_OAMethod @objServer, 'Connect', NULL, @SourceServer
if @SourceUID is not null and @hr=0 exec @hr = sp_OAMethod @objServer, 'Connect', NULL, @SourceServer ,@SourceUID
end
else
Begin
if @hr=0 SELECT @strErrorMessage = 'Connecting to '''+@SourceServer+
''' with user ID '''+@SourceUID+'''', @objErrorObject=@objServer
if @hr=0 exec @hr = sp_OAMethod @objServer, 'Connect', NULL, @SourceServer ,
@SourceUID , @SourcePWD
end
if @hr=0 Select @strErrorMessage='finding the number of databases in '+@SourceServer,
@objErrorObject=@objServer
if @HR=0 EXEC @hr = sp_OAGetProperty @objServer, 'databases.Count',
@iimax OUT
Select @ii=1
while @hr=0 and @ii<=@iiMax
begin
Select @strErrorMessage='Getting each item'
select @command='databases.item('+cast (@ii as varchar)+').name'
EXEC @hr = sp_OAGetProperty @objServer, @command,
@SourceDB OUT
if (@hr=0 and @SourceDB like @DatabaseWildcard)
begin /* then we script it out */
select @command='databases.item('+cast (@ii as varchar)+')'
EXEC @hr = sp_OAGetProperty @objServer, @command,
@objSpecificDB OUT
-- Create transfer object
if @hr=0 SELECT @strErrorMessage = 'Creating a DMO transfer object'
if @hr=0 exec sp_OACreate 'SQLDMO.Transfer', @objTransfer OUT -- Create Object
if @hr=0 SELECT @strErrorMessage =
'Selecting a property of the transfer',
@objErrorObject=@objTransfer
if @hr=0 EXEC @hr= sp_OASetProperty @objTransfer, 'CopySchema',1
if @hr=0 EXEC @hr= sp_OASetProperty @objTransfer, 'CopyAllObjects',1
if @hr=0 EXEC @hr= sp_OASetProperty @objTransfer, 'CopyData',0
/*If a path is not included in the file name, the
file is created in the directory indicated by the client computer
environment variable TEMP. */
if @ScriptfileMode in (2,8)
begin
Select @WorkDIR=rtrim(@WorkDIR)
select @strFilename=@WorkDIR+
case when right(@WorkDIR,1)'\' then '\' else '' end+
coalesce(@Workfile,@SourceServer+'_'+@SourceDB+'.SQL')
end
else
begin
select @strFilename=@WorkDIR+
case when right(@WorkDIR,1)'\' then '\' else '' end+
'\'+@SourceDB
select @command='MD '+@strFilename
execute master..xp_cmdshell @command
end
if @hr=0 SELECT @strErrorMessage =
'Assigning the transfer object to '+@SourceDB+' to script out to '+
@Strfilename,
@objErrorObject=@objSpecificDB
if @hr=0
insert into #Junk(line)
EXEC @hr= sp_OAMethod @objSpecificDB,'ScriptTransfer',
NULL,@objTransfer,@ScriptFileMode,@strFilename
EXEC sp_OADestroy @objTransfer --create it anew every time
end
Select @ii=@ii+1
end
if @hr0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int
EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '+@strErrorMessage+', '+@Description
raiserror (@strErrorMessage,16,1)
end
EXEC sp_OADestroy @objSpecificDB
EXEC sp_OADestroy @objDatabases
EXEC sp_OADestroy @objServer
return @hr
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Alex Graham 