Automated Database Schema Scripting (DMO)

Tuesday Aug 24th 1999 by Bill Wunder
Share:

Having scripts to recreate your SQL Server environment is a crucial part of a good backup/recovery plan. It is possible to use the GUI to 'Generate SQL Scripts...'. This can be time consuming and prone to the drudgery and errors of manual procedures. It is not possible to schedule script creation using the GUI. SQL_DMO is a useful to automate the process and assure the quality and consistency of the scripts produced.

Having scripts to recreate your SQL Server environment is a crucial part of a good backup/recovery plan. It is possible to use the GUI to "Generate SQL Scripts...". This can be time consuming and prone to the drudgery and errors of manual procedures. It is not possible to schedule script creation using the GUI. SQL_DMO is a useful to automate the process and assure the quality and consistency of the scripts produced.

Here are a set of stored procedures that will script the objects in a database for use in recreating or documenting that database.

There are two driver procedures. One to script collections of the server object (dmoScriptServer) and another to script collections of the database object (dmoScriptDatabase). Select either driver name for a walk through of the code for that process.

The server objects that support the script method are "Jobserver.Jobs", "Logins" and "Database". I encountered numerous problems with the "Database" object's script method and have decided to use an alternate method to produce the CREATE DATABASE scripts. Also, I will still need to build a script that sets the passwords for the logins to the correct values in the event I have to use the script(s) produced from the Logins object. This password script will have to be built manually and kept in a secure location.

The database object is much more effective in producing good scripts. The collections in the database include tables, stored procedures, views, databaseroles, and views. There are several collections within each table object (indexes, constraints, triggers, DRI, ...). One unfortunate issue that was reported is that objects that don't belong to dbo are not scripted. I don't have this problem because I don't have any objects that aren't owned by dbo.

Instructions for installing the scripts.

Load these scripts onto a SQL7 Standard or Enterprise. They won't work on Desktop Edition SQL Server because trusted security is used throughout. (You can modify to use SQL Server Security by changing the dmoConnect and modifying the single osql call, located in dmoScriptDatabases, to include a login name and password

These scripts were all produced with the utility. I decided not to put them in a zip file to FORCE you to have to look at the code.

A database to hold the process:
admin
Two tables needed for the CREATE DATABASE workaround:
databaseFileInfo
databaseFileGroupInfo
A handful of stored procedures:
script_user_xp
dmoConnectServer
dmoScriptServer
dmoScriptServerCollection
dmoScriptDatabase
dmoScriptDatabaseCollection
dmoScriptTables
dmoScriptTableCollection
sp_displayoaerrorinfo (from BOL - mostly)
sp_hexadecimal (from BOL)
ScriptCreateDatabase
dmoDisconnectServer

Create the database - use the script or just simply create a database with the defaults using the wizard. I will be posting additional scripts for inclusion into this database as time permits.

Create the tables - The tables should never have more than a dozen rows and are truncated before each use, so indexes are not useful.

Create the Stored Procedures.

Determine the path to where you want the scripts to be stored. Create and non existing folders to establish the path.

This how you call the procedures. Replace the word in the brackets and the brackets with valid values from your environment. If you want to script logins make a call to

exec admin..dmoScriptServer [server name], [output path]
If you want to script a database make a call to
exec admin..dmoScriptDatabase [server name], [database name], [output path]
If you want to script each object to a separate file the command would look like:
exec admin..dmoScriptServer [server name], [output path], 1
or
exec admin..dmoScriptDatabase [server name], [database name], [output path] , 1

Let me know if you have any problems , questions, or corrections.

Modify the <filespec> settings to suit your environment. For most folks, the file path will be the only required modification.

CREATE DATABASE admin
 ON PRIMARY
 ( NAME = admin_Data,
   FILENAME = 'D:\MSSQL7\data\admin_Data.MDF',
   SIZE = 1600 KB,
   MAXSIZE = Unlimited,
   FILEGROWTH = 10% )
 LOG ON
 ( NAME = admin_Log,
   FILENAME = 'd:\MSSQL7\data\admin_Log.LDF',
   SIZE = 1024 KB,
   MAXSIZE = Unlimited,
   FILEGROWTH = 10% )  


CREATE TABLE [databaseFileInfo] (
   [name] [varchar] (128) NULL ,
   [fileid] [varchar] (20) NULL ,
   [filename] [varchar] (255) NULL ,
   [filegroup] [varchar] (128) NULL ,
   [size] [varchar] (20) NULL ,
   [maxsize] [varchar] (20) NULL ,
   [growth] [varchar] (20) NULL ,
   [usage] [varchar] (20) NULL 
) ON [PRIMARY]
GO







CREATE TABLE [databaseFileGroupInfo] (
   [groupname] [varchar] (128) NULL ,
   [groupid] [varchar] (20) NULL ,
   [filecount] [varchar] (20) NULL 
) ON [PRIMARY]
GO












DECLARE @procedure varchar(255),                                                                    
         @objectName varchar(30),                                                                    
         @file varchar(255),                                                                         
         @user varchar(30)                                                                           
 SET NOCOUNT ON                                                                                      
  DECLARE object_cursor CURSOR
  FOR                                                                                                
  SELECT DISTINCT o.name, c.text, u.name                                                             
  FROM master..sysobjects o, master..syscomments c,                                                  
       master..sysprotects p, master..sysusers u                                                     
  WHERE c.id = o.id                                                                                  
  AND p.id = o.id                                                                                    
  AND p.uid = u.uid                                                                                  
  AND o.type = "X"                                                                                   
  AND o.category = 0                                                                                 
 OPEN object_cursor                                                                                  
   FETCH NEXT FROM object_cursor INTO @objectName, @file, @user                                      
     WHILE (@@fetch_status <> -1)                                                                    
       BEGIN                                                                                         
         SELECT @procedure = "EXECUTE sp_addextendedproc " + CHAR(39) +                              
                             @objectName + CHAR(39) + ", " + CHAR(39) +                              
                             @file + CHAR(39)                                                        
         PRINT @procedure                                                                            
         PRINT "GO"                                                                                  
         SELECT @procedure = "GRANT EXECUTE  ON master.." +                                          
                             @objectName + " TO " + @user                                            
         PRINT @procedure                                                                            
         PRINT "GO"                                                                                  
         PRINT ""                                                                                    
     	 FETCH NEXT FROM object_cursor INTO @objectname, @file, @user                                  
      END                                                                                            
 DEALLOCATE object_cursor






CREATE procedure dmoConnectServer
  @serverName varchar(30),
  @dmoServer int OUT
as
declare @hr int
declare @password varchar(30)

set nocount on

/* create a server object */
exec @hr = master..sp_OACreate 'SQLDMO.SQLServer', @dmoServer OUT
if @hr <> 0 
  begin
    exec sp_displayoaerrorinfo @dmoServer, @hr
    return  
  end

/* set the security context to integrated */
exec @hr = master..sp_OASetProperty @dmoServer,'loginSecure',1
if @hr <> 0 
  begin
    exec sp_displayoaerrorinfo @dmoServer, @hr
    return
  end

/* connect to the specified server */
exec @hr = master..sp_OAMethod @dmoServer,'Connect',NULL,@serverName
if @hr <> 0 
  begin
    exec sp_displayoaerrorinfo @dmoServer, @hr
    exec dmoDisconnectServer @dmoServer
    select @dmoServer=0
    return 
  end

return

This represents the basic access method to the DMO object model.
Connect -> Access Object Model -> Disconnect

CREATE procedure dmoScriptServer
  @serverName varchar(30) ,
  @outputPath varchar(255) ,
  @style bit = 0
as
declare @dmoServer int

-- Check for trailing slash on backup location param
if (substring(@outputPath, len(@outputPath), 1) <> '\') 
  begin
    set @outputPath = @outputPath + '\'
  end

/* connect to the server where database to be scripted lives */
exec dmoConnectServer @serverName, @dmoServer OUT

if @dmoServer > 0
  begin
    exec dmoScriptServerCollection @serverName, @dmoServer, 'Logins',@outputPath, @style
    exec dmoScriptServerCollection @serverName, @dmoServer, 'Jobserver.Jobs',@outputPath, @style

/* the DMO database scripter is broken. Does not produce useable scripts and may blow up in some situations. 
  An alternate database scripter uses a non dmo routine to script each individual database 
  and it is called from inside the database via dmoScriptDatabase*/    
--    exec dmoScriptServerCollection @serverName, @dmoServer, 'Databases',@outputPath, @style

    exec dmoDisconnectServer @dmoServer
  end

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



CREATE procedure dmoScriptDatabase
  @serverName varchar(30),
  @databaseName varchar(30),
  @outputPath varchar(255) = 'c:\',
  @style tinyint = 0
as
declare @objectName varchar(255)
declare @dmoServer int
declare @cmd varchar(255)

/* uses a trusted osql connetion back to the admin database to create an output file
    containing the CREATE DATABASE statement for the specified database */
select @cmd = 'osql -U sa -E -S ' + @@SERVERNAME + ' -Q "admin.dbo.ScriptCreateDatabase '+ 
                        @serverName +', ' + @databaseName + '" -o '+ @outputPath + @serverName + '_Databases_' + @databaseName +'.sql -h-1 -w2000' 
exec master..xp_cmdshell @cmd 

/* use Integrated Security for the OLE process */
exec dmoConnectServer @serverName, @dmoServer OUT

if @dmoServer > 0
  begin
    /* each table object has several collections to process */
    exec dmoScriptTables @dmoServer, @serverName, @databaseName, @outputPath, @style

    /* other database objects can use a generic script generator */
    exec dmoScriptDatabaseCollection @dmoServer, @serverName,  @databaseName, 'Users', @outputPath, @style
    exec dmoScriptDatabaseCollection @dmoServer, @serverName,  @databaseName, 'DatabaseRoles', @outputPath, @style
    exec dmoScriptDatabaseCollection @dmoServer, @serverName, @databaseName, 'StoredProcedures', @outputPath, @style
    exec dmoScriptDatabaseCollection @dmoServer, @serverName, @databaseName, 'Views', @outputPath, @style
    exec dmoScriptDatabaseCollection @dmoServer, @serverName, @databaseName, 'Rules', @outputPath, @style 
    exec dmoScriptDatabaseCollection @dmoServer, @serverName,  @databaseName, 'Defaults', @outputPath, @style
    exec dmoScriptDatabaseCollection @dmoServer, @serverName, @databaseName, 'UserDefinedDatatypes', @outputPath, @style 

    /* a little clean up */
    exec dmoDisconnectServer @dmoServer
  end



CREATE procedure dmoScriptDatabaseCollection
  @dmoServer int,
  @serverName varchar(255),
  @databaseName varchar(255),
  @collectionName varchar(255),
  @path varchar(255),
  @style int = 0,
  @debug bit = 0
as
/*
  Use this procedure to generate scripts useable to recreate the objects within 
  a database object or the JobServer object 
  STYLE - 0 means to pu all objects in one file, 1 means to put each object in its 
  own file
*/
declare @dmoMethod varchar(255) 
declare @dmoProperty varchar(255) 
declare @file varchar(255)
declare @hr int
declare @boolSystemObject tinyint
declare @objectCount int
declare @curObjectNb int
declare @objectName varchar(256) 
declare @osCmd varchar(255)
declare @msg varchar(255)

set nocount on

If @collectionName not in ("DatabaseRoles","Defaults","FileGroups","FullTextCatalogues","Rules",
          "StoredProcedures","SystemDatatypes","Tables","TransactionLog","UserDefinedDatatypes","Users","Views")
  begin
    select @msg = "The " + @collectionName + " collection is not available from the 
 database object. (admin..dmoScriptDatabaseCollection)"
    raiserror (@msg, 16, 1)
    return
  end

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

/* get the number of elements in the collection */
select @dmoProperty = 'Databases("' + @databaseName + '").' + @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
    if @collectionName in ("Tables", "StoredProcedures","Views","Triggers","Users")
      begin
        /* only script user objects */
        select @boolSystemObject = 0    
        select @dmoProperty = 'Databases("' + @databaseName + '").' + @collectionName
 + '.Item(' + convert(varchar(5),@curObjectNb) + ').SystemObject'
        exec @hr = master..sp_OAGetProperty @dmoServer,@dmoProperty,@boolSystemObject OUT
        if @hr <> 0 
          begin
            exec sp_displayoaerrorinfo @dmoServer, @hr
            return
          end
      end

    if @boolSystemObject = 0
      begin
        /* get the object name */
        select @dmoProperty = 'Databases("' + @databaseName + '").' + @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 = 'Databases("' + @databaseName + '").' + @collectionName + '("' + @objectName + '").Script'
        if @style = 0 
          begin  
            /* put all object scripts in a single file */
            exec @hr = master..sp_OAMethod @dmoServer,@dmoMethod,NULL,295,@file
          end
        else
          begin 
            /* put each object script in its own file */
            select @file = @path + @serverName + "_Database_" + @databaseName + "_" + 
substring(@collectionName,1,DATALENGTH(@collectionName)-1)  + "_" + @objectName + ".sql"
            exec @hr = master..sp_OAMethod @dmoServer,@dmoMethod,NULL,39,@file
          end
        if @hr <> 0 
          begin
            exec sp_displayoaerrorinfo @dmoServer, @hr
            return
          end
      end
    select @curObjectNb = @curObjectNb + 1
  end



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


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 

CREATE PROCEDURE sp_displayoaerrorinfo
    @object int,
    @hresult int
AS
/* from BOL  OLE Automation Return Codes and Error Information
   This document is a link on the sp_OAGetErrorInfo help page */
DECLARE @output varchar(255)
DECLARE @hrhex char(10)
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(255)
PRINT 'OLE Automation Error Information'
EXEC sp_hexadecimal @hresult, @hrhex OUT
SELECT @output = '  HRESULT: ' + @hrhex
PRINT @output
EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = '  Source: ' + @source
PRINT @output
SELECT @output = '  Description: ' + @description
PRINT @output
END
ELSE
BEGIN
    PRINT "  sp_OAGetErrorInfo failed."
    RETURN
END















CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(255),
    @hexvalue varchar(255) OUTPUT
AS
/* from BOL  OLE Automation Retrn COdes and Error Information
   This document is a link on the sp_OAGetErrorInfo help page */
DECLARE @charvalue varchar(255)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH(@binvalue)
SELECT @hexstring = '0123456789abcdef'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue

create procedure ScriptCreateDatabase
  @serverName varchar(255),
  @databaseName varchar(255)
as
declare @groupid varchar(20)
declare @fileid varchar(20)
declare @script varchar(2000)
declare @cmd varchar(255)

set nocount on

/* hold semaphore */

delete admin..databaseFileGroupInfo
delete admin..databaseFileInfo

select @cmd = @serverName + '.' + @databaseName + '.dbo.sp_helpfilegroup'
insert admin..databaseFileGroupInfo exec @cmd
select @cmd = @serverName + '.' + @databaseName + '.dbo.sp_helpfile'
insert admin..databaseFileInfo exec @cmd

select @script = 'CREATE DATABASE ' + @databaseName 

  /* loop through the filegroups looking for data files */
  select @groupid = MIN(groupid)
  from admin..databaseFileGroupInfo
  where groupname in (select filegroup 
                      from admin..databaseFileInfo
                      where usage not like '%log%')

  while @groupid is not null
    begin
      select @script = @script + '
 ON ' + rtrim(ltrim(groupname))
      from admin..databaseFileGroupInfo
      where groupid = @groupid
      
      /* loop through the files in this group */
      select @fileid = MIN(fileid)
      from admin..databaseFileInfo
      where filegroup = (select groupname 
                         from admin..databaseFileGroupInfo
                         where groupid = @groupid)
      and usage not like '%log%'
      while @fileid is not null
        begin
          select @script = @script + '
 ( NAME = ' + rtrim(ltrim(name)) + ',
   FILENAME = ''' + rtrim(ltrim(filename)) + ''',
   SIZE = ' + rtrim(ltrim(size)) + ',
   MAXSIZE = ' + rtrim(ltrim(maxsize)) + ',
   FILEGROWTH = ' + rtrim(ltrim(growth)) + ' )'
          from admin..databaseFileInfo
          where fileid = @fileid
          and filegroup = (select groupname 
                           from admin..databaseFileGroupInfo
                           where groupid = @groupid)
          and usage not like '%log%'

          select @fileid = MIN(fileid)
          from admin..databaseFileInfo
          where fileid > @fileid
       end
    select @groupid = MIN(groupid)
    from admin..databaseFileGroupInfo
    where groupid > @groupid
    and groupname in (select filegroup 
                      from admin..databaseFileInfo
                      where usage not like '%log%')
  end
  /* script the log file -- assume there is only one */
  select @script = @script + '
 LOG ON
 ( NAME = ' + rtrim(ltrim(name)) + ',
   FILENAME = ''' + rtrim(ltrim(filename)) + ''',
   SIZE = ' + rtrim(ltrim(size)) + ',
   MAXSIZE = ' + rtrim(ltrim(maxsize)) + ',
   FILEGROWTH = ' + rtrim(ltrim(growth)) + ' )'
  from admin..databaseFileInfo
  where filegroup is null
  and usage like '%log%' 

select @script










CREATE procedure dmoDisconnectServer
  @dmoServer int
as
declare @hr int

set nocount on

exec @hr = master.dbo.sp_OAMethod @dmoServer,'DisConnect'
if @hr <> 0 
    exec sp_displayoaerrorinfo @dmoServer, @hr

/* close the database connection */
exec @hr = master.dbo.sp_OADestroy @dmoServer
if @hr <> 0 
  begin
    exec sp_displayoaerrorinfo @dmoServer, @hr
    return
  end

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved