Automated Database Schema Scripting (DMO) - Page 10

CREATE procedure dmoScriptDatabaseCollection
  @dmoServer int,
  @serverName varchar(255),
  @databaseName varchar(255),
  @collectionName varchar(255),
  @path varchar(255),
  @style int = 0,
  @debug bit = 0
as
/*
  Use this procedure to generate scripts useable to recreate the objects within 
  a database object or the JobServer object 
  STYLE - 0 means to pu all objects in one file, 1 means to put each object in its 
  own file
*/
declare @dmoMethod varchar(255) 
declare @dmoProperty varchar(255) 
declare @file varchar(255)
declare @hr int
declare @boolSystemObject tinyint
declare @objectCount int
declare @curObjectNb int
declare @objectName varchar(256) 
declare @osCmd varchar(255)
declare @msg varchar(255)

set nocount on

If @collectionName not in ("DatabaseRoles","Defaults","FileGroups","FullTextCatalogues","Rules",
          "StoredProcedures","SystemDatatypes","Tables","TransactionLog","UserDefinedDatatypes","Users","Views")
  begin
    select @msg = "The " + @collectionName + " collection is not available from the 
 database object. (admin..dmoScriptDatabaseCollection)"
    raiserror (@msg, 16, 1)
    return
  end

/* clean up existing files */
if @style = 0 
  begin
    select @file = @path + @serverName + "_Database_" + @databaseName + "_" + @collectionName + ".sql"
    select @osCmd = "del " + @file
    exec master..xp_cmdshell @osCmd, no_output
  end

/* get the number of elements in the collection */
select @dmoProperty = 'Databases("' + @databaseName + '").' + @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
    if @collectionName in ("Tables", "StoredProcedures","Views","Triggers","Users")
      begin
        /* only script user objects */
        select @boolSystemObject = 0    
        select @dmoProperty = 'Databases("' + @databaseName + '").' + @collectionName
 + '.Item(' + convert(varchar(5),@curObjectNb) + ').SystemObject'
        exec @hr = master..sp_OAGetProperty @dmoServer,@dmoProperty,@boolSystemObject OUT
        if @hr <> 0 
          begin
            exec sp_displayoaerrorinfo @dmoServer, @hr
            return
          end
      end

    if @boolSystemObject = 0
      begin
        /* get the object name */
        select @dmoProperty = 'Databases("' + @databaseName + '").' + @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 = 'Databases("' + @databaseName + '").' + @collectionName + '("' + @objectName + '").Script'
        if @style = 0 
          begin  
            /* put all object scripts in a single file */
            exec @hr = master..sp_OAMethod @dmoServer,@dmoMethod,NULL,295,@file
          end
        else
          begin 
            /* put each object script in its own file */
            select @file = @path + @serverName + "_Database_" + @databaseName + "_" + 
substring(@collectionName,1,DATALENGTH(@collectionName)-1)  + "_" + @objectName + ".sql"
            exec @hr = master..sp_OAMethod @dmoServer,@dmoMethod,NULL,39,@file
          end
        if @hr <> 0 
          begin
            exec sp_displayoaerrorinfo @dmoServer, @hr
            return
          end
      end
    select @curObjectNb = @curObjectNb + 1
  end