Automated Database Schema Scripting (DMO) - Page 15

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