Automated Database Schema Scripting (DMO) - Page 11

CREATE procedure dmoScriptTables
  @dmoServer int,
  @serverName varchar(255),
  @databaseName varchar(255),
  @path varchar(255),
  @style int = 0
as
declare @dmoMethod varchar(255) 
declare @dmoProperty varchar(255) 
declare @dmoCollection varchar(255) 
declare @file varchar(255)
declare @hr int
declare @boolSystemTable tinyint
declare @tableCount int
declare @curTableNb int
declare @tableName varchar(256)
declare @osCmd varchar(255)

set nocount on

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

/* generate script for each user table */

/* get the number of elements in the tables collection */
select @dmoProperty = 'Databases("' + @databaseName + '").Tables.Count'
exec @hr = master..sp_OAGetProperty @dmoServer,@dmoProperty,@tableCount OUT
if @hr <> 0 
  begin
    exec sp_displayoaerrorinfo @dmoServer, @hr
    return
  end
select @curTableNb = 1
while @curTableNb <= @tableCount   
  begin
    select @dmoProperty = 'Databases("' + @databaseName + '").Tables.Item(' + convert(varchar(5),@curTableNb) + ').SystemObject'
    exec @hr = master..sp_OAGetProperty @dmoServer,@dmoProperty,@boolSystemTable OUT
    if @hr <> 0 
      begin
        exec sp_displayoaerrorinfo @dmoServer, @hr
      return
    end  
    if @boolSystemTable = 0 -- not a system table
      begin
        /* get the table name */
        select @dmoProperty = 'Databases("' + @databaseName + '").Tables.Item(' + convert(varchar(5),@curTableNb) + ').Name'
        exec @hr = master..sp_OAGetProperty @dmoServer,@dmoProperty,@tableName OUT
        if @hr <> 0 
          begin
            exec sp_displayoaerrorinfo @dmoServer, @hr
            return
          end  
        select @dmoMethod = 'Databases("' + @databaseName + '").Tables("' + @tableName + '").Script'
       if @style = 0
         begin
          /* put all tables in a single script file  */
          select @file = @path + @serverName + "_Database_" + @databaseName + "_Tables.sql"
          exec @hr = master..sp_OAMethod @dmoServer,@dmoMethod,NULL,359,@file
        end
      else
        begin
          /* put each table script in its own file */
          select @file = @path + @serverName + "_Database_" + @databaseName + "_Table_" + @tableName + ".sql"
          exec @hr = master..sp_OAMethod @dmoServer,@dmoMethod,NULL,69,@file
        end
        if @hr <> 0 
          begin
            exec sp_displayoaerrorinfo @dmoServer, @hr
            return
          end

        /* script all indexes for this table */
       exec dmoScriptTableCollection @dmoServer, @serverName, @databaseName, @tableName, 'Indexes', @path, @style  

        /* script all triggers for this table */
       exec dmoScriptTableCollection @dmoServer, @serverName, @databaseName, @tableName, 'Triggers', @path, @style  

      end
    select @curTableNb = @curTableNb + 1
  end