use userbase
go

create procedure getScriptCreateDatabase @dbname varchar( 20 ), @for_load char( 1 ), @is_debug char( 1 )
as
begin
select
        dbname=d.name,
        datalog=case
                when u.segmap =  8  then 'd'
                when u.segmap =  7  then 'd'
                when u.segmap =  4  then 'l'
                when u.segmap =  3  then 'd'
                when u.segmap =  2  then 'd'
                when u.segmap =  1  then 'd'
                when u.segmap =  0  then 'l'
        end,
        devname=v.name,
        size=convert(varchar,((u.size)*(@@maxpagesize/ 1024 )/ 1024 ), 10 )+'M'
into tmpTable
  from master..sysusages u, master..sysdatabases d, master..sysdevices v
  where u.dbid=d.dbid and
        u.vstart >= v.low and
        u.vstart <= v.high and
        v.name not like 'tapedump%' and
        d.name=@dbname
  order by u.dbid,u.lstart
if @is_debug='1'
        select * from tmpTable
set rowcount  1 
declare @vdatalog char( 1 )
declare @vdevname_dat varchar( 20 )
declare @vdevname_log varchar( 20 )
declare @vsize_dat varchar( 10 )
declare @vsize_log varchar( 10 )
declare @create char( 1 )
select @create='1'
declare @prefix_dat varchar( 10 )
declare @prefix_log varchar( 30 )
declare @postfix varchar( 15 )
if @for_load='0'
        select @postfix=''
else
        select @postfix=' for load'
while (select count(*) from tmpTable) <>  0 
        begin
        select @vdevname_dat=devname, @vsize_dat=size from tmpTable where datalog='d'
        select @vdevname_log=devname, @vsize_log=size from tmpTable where datalog='l'
        if @create='1'
                begin
                select @prefix_dat='create'
                select @prefix_log=''
                end
        else
                begin
                select @prefix_dat='alter'
                select @prefix_log='alter database ' + @dbname
                end
        if isnull(@vdevname_dat, '0') <> '0' and isnull(@vdevname_log, '0') <> '0' and @create='1'
                begin
                if @is_debug='1'
                        select 'BOTH'
                select @prefix_dat + ' database ' + @dbname + ' on ' + @vdevname_dat + '=''' + @vsize_dat + '''' +
                        @prefix_log + ' log on ' + @vdevname_log + '=''' + @vsize_log  +'''' + @postfix + char( 10 ) + 'go'
                delete from tmpTable where devname=@vdevname_dat and size=@vsize_dat and datalog='d'
                select @vdevname_dat=NULL
                delete from tmpTable where devname=@vdevname_log and size=@vsize_log and datalog='l'
                select @vdevname_log=NULL
                end
        else
                begin
                if isnull(@vdevname_dat, '0') <> '0'
                        begin
                        if @is_debug='1'
                                select 'DAT'
                        select @prefix_dat + ' database ' + @dbname + ' on ' + @vdevname_dat + '=''' + @vsize_dat + ''''  + @postfix + char( 10 ) + 'go'
                        delete from tmpTable where devname=@vdevname_dat and size=@vsize_dat and datalog='d'
                        select @vdevname_dat=NULL
                        end
                if isnull(@vdevname_log, '0') <> '0'
                        begin
                        if @is_debug='1'
                                select 'LOG'
                        select @prefix_log + ' log on ' + @vdevname_log + '=''' + @vsize_log + ''''  + @postfix + char( 10 ) + 'go'
                        delete from tmpTable where devname=@vdevname_log and size=@vsize_log and datalog='l'
                        select @vdevname_log=NULL
                        end
                end
        if @create='1'
                select @create='0'
        end
set rowcount  0 
drop table tmpTable
end

create database simm2 on master='4M'  log on simm2_log='1M'
go
alter database simm2 on simm2_data='2M'
go
alter database simm2 log on simm2_log='2M'
go

create database simm2 on my_new_big_data_dev='4M'  log on my_new_big_log_dev='1M'
go
alter database simm2 on my_new_big_data_dev='2M'
go
alter database simm2 log on my_new_big_log_dev='2M'
go