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 12

By Bill Wunder


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 



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