Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 24, 1999

Automated Database Schema Scripting (DMO) - Page 11

By Bill Wunder


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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM