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 8

By Bill Wunder

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




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


















Thanks for your registration, follow us on our social networks to keep up-to-date