Automated Database Schema Scripting (DMO) - Page 12

CREATE procedure dmoScriptTableCollection
  @dmoServer int,
  @serverName varchar(255),
  @databaseName varchar(255),
  @tableName varchar(255),
  @dmoCollection varchar(255),
  @path varchar(255),
  @style int = 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)

set nocount on

/* establish the file name based on the naming convention */
if @style = 0 
  select @file = @path + @serverName + "_Database_" + @databaseName + "_Tables.sql"
else
  select @file = @path + @serverName + "_Database_" + @databaseName + "_Table_" + @tableName + ".sql"


/* generate script(s) for the collection */
/* script all indexes for this table */
select @dmoProperty = 'Databases("' + @databaseName + '").Tables("' + @tableName + '").' + @dmoCollection + '.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 = 'Databases("' + @databaseName + '").Tables("' + @tableName + '").' 
	  + @dmoCollection + '.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 + '").Tables
	  ("' + @tableName + '").' + @dmoCollection + '("' + @objectName + '").Script'
      if @style = 0
        begin
          /* put all indexes in the table script file  */       
          exec @hr = master..sp_OAMethod @dmoServer,@dmoMethod,NULL,439,@file
        end
      else
        begin
          /* put each object's script in a table specific file */
          exec @hr = master..sp_OAMethod @dmoServer,@dmoMethod,NULL,439,@file
        end
      if @hr <> 0 
        begin
          exec sp_displayoaerrorinfo @dmoServer, @hr
          return
        end
      select @curObjectNb = @curObjectNb + 1 
    end