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 15

By Bill Wunder

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



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