Automated Database Schema Scripting (DMO) - Page 8

CREATE procedure dmoScriptServerCollection
  @serverName varchar(255),
  @dmoServer int,
  @collectionName varchar(255),
  @path varchar(255),
  @style bit = 0
as
declare @dmoMethod varchar(255) 
declare @dmoProperty varchar(255) 
declare @file varchar(255)
declare @hr int
declare @objectCount int
declare @curObjectNb int
declare @objectName varchar(256) 
declare @osCmd varchar(255)

set nocount on

/* build the file name */
if @style = 0
  begin
    select @file = @path + @serverName + "_" + @collectionName + ".sql"

    /* remove any existing file by this name */
    select @osCmd = "del " + @file
    exec master..xp_cmdshell @osCmd, no_output
  end

/* get the number of elements in the collection */
select @dmoProperty = @collectionName + '.Count'
exec @hr = master..sp_OAGetProperty @dmoServer,@dmoProperty,@objectCount OUT
if @hr <> 0 
  begin
    exec sp_displayoaerrorinfo @dmoServer, @hr
    return
  end

select @curObjectNb = 1
while @curObjectNb <= @ObjectCount 
  begin
    /* get the object name */
    select @dmoProperty = @collectionName + '.Item(' + convert(varchar(5),@curObjectNb) + ').Name'
    exec @hr = master..sp_OAGetProperty @dmoServer,@dmoProperty,@objectName OUT
    if @hr <> 0 
      begin
        exec sp_displayoaerrorinfo @dmoServer, @hr
        return
      end  

    select @dmoMethod = @collectionName + '("' + @objectName + '").Script'
    if @style = 0
      begin    
        /* append all object scripts to a file */
        exec @hr = master..sp_OAMethod @dmoServer,@dmoMethod,NULL,325,@file
      end
    else
      begin
        select @file = @path + @serverName + "_" + 
                       substring(@collectionName,1,datalength(@collectionName)-1) + 
                       "_" + @objectName + ".sql"
          exec @hr = master..sp_OAMethod @dmoServer,@dmoMethod,NULL,5,@file
      end
    if @hr <> 0 
      begin
        exec sp_displayoaerrorinfo @dmoServer, @hr
        return
      end    
    select @curObjectNb = @curObjectNb + 1

  end