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 @hr<>0
	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