Very Computer

Could anyone who has a nifty script to automatically
generate CREATE DATABASE sql scripts for a given
SQL SERVER - please post?

We assume that sysusages is taken into account.

Thanks a lot!

We were going to write this ourself, but I'm sure it's
been done more than 1 thousand times!

HENRY KIM

Get a hold of Sybperl 2.03 (see http://www.sybase.com for pointers).
There are a couple of really nice sybperl programs called dbschema.pl
and dbschema.10.pl in the eg directory which will analyze your existing
database and generate the isql source for it. I ran them on our 10.0.2
server and they appear to work well.

--
-------------------------------------------------------------------
Gary F. Alderson              :   Phone: (204) 474-9910
Mgr, Application Technologies :     Fax: (204) 275-1493

University of Manitoba        :------------------------------------
Winnipeg, Manitoba            : Home Page URL:
Canada     R3T-2N2            : http://Home.cc.UManitoba.CA/~alders
-------------------------------------------------------------------

Your wish is my command, Oh Lazy One.  

CAVEAT:  Written ages ago.  Minimally tested before upload.  Clunky code.
 Test it yourself and /or adapt it.  
You owe me a beer.  

simon walker
The SQL Workshop
Milton Keynes, UK
siwal...@cix.compulink.co.uk

---------8<-------------------

/************************************************************************

 sp_crdb

 Output a set of CREATE and ALTER DATABASE commands that would reproduce
 the specified database.  

 1995, The SQL Workshop, 31 Newport Road, Milton Keynes, MK15 0AD, UK
 +44 (0)1908 677117     siwal...@cix.compulink.co.uk

 Who    When    Why
 ------------------------------------------------------------------------
 simon  agesago created
 walker

*************************************************************************/

if exists(select * from master..sysdatabases where name =
"sybsystemprocs")
        use sybsystemprocs
else
        use master
go

if exists(select * from sysobjects where name = "sp__crdb" and type = "P")
        drop procedure sp__crdb
go

create procedure sp__crdb

        @dbname char(30)
as

declare @lstart         int,
        @dbid           int,
        @first_data     tinyint,
        @first_log      tinyint,
        @created        tinyint,
        @msg            varchar(255),
        @size           float,
        @device         varchar(30),
        @segmap         int,
        @status         smallint

select  @dbid = dbid,
        @first_log = 1,
        @first_data = 1,
        @created = 0
from    master..sysdatabases
where   name = @dbname

if @@ROWCOUNT =0
begin
        print "Database does not exist"
        return (1)
end

print   "use master"
print   "go"
print   ""

select  @lstart = min(lstart)
from    master..sysusages
where   dbid = @dbid

select @msg = "create database "+ltrim(@dbname) + " on "
print @msg

while (@lstart is NOT NULL)
begin
        select  @size = size*2048.0/1024.0/1024.0,
                @device = d.name,
                @segmap = number
        from    master..sysusages u,
                master..sysdevices d,
                master..spt_values v
        where   dbid = @dbid
        and     lstart = @lstart
        and     vstart between d.low and d.high
        and     segmap & 7 = number
        and     status & 2 =2
        and     type = "S"

        if @segmap != 4 and @first_log = 1      /* data only  and not yet
found any log*/
        begin
                if @first_data = 1
                begin
                        select @msg = char(9)+" "+@device + " = " +
ltrim(str(@size,6))
                        print @msg
                end
                else
                begin
                        select @msg = char(9)+","+@device + " = " +
ltrim(str(@size,6))
                        print @msg
                end

                select @first_data = 0
        end

        if @segmap not in (4,7) and @first_log = 0      /* data only  and
have done some log*/
        begin
                if @created = 0
                        print "go"
                select @created = 1
                select @msg = "alter database "+ltrim(@dbname)+" on
"+ltrim(@device)+" = "+ltrim(str(@size,6))
                print @msg
                print "go"
        end

        if @segmap = 4 and @created = 0         /* log only and still in
main create statement */
        begin
                if @first_log = 1
                begin
                        print "log on"
                        select @msg =  char(9) + " " + @device + " = " +
ltrim(str(@size,6))
                        print @msg
                end
                else
                begin
                        select @msg =  char(9) + ","+@device + " = " +
ltrim(str(@size,6))
                        print @msg
                end
                select @first_log = 0
        end                    

        if @segmap = 4 and @created = 1         /* log only and main
create done */
        begin
                select @msg =  "alter database "+ltrim(@dbname)+" on
"+ltrim(@device)+" = "+ltrim(str(@size,6))
                print @msg
                print "go"
                select @msg = "execute
"+rtrim(@dbname)+"..sp_extendsegment logdevice, " + rtrim(@dbname)+", " +
ltrim(@device)
                print @msg
                print "go"
        end

        select  @lstart = min(lstart)
        from    master..sysusages
        where   dbid = @dbid
        and     lstart > @lstart
end

select  @status = status
from    master..sysdatabases
where   name = @dbname

if @status & 4 = 4
begin
        select  @msg = "execute sp_dboption " + rtrim(@dbname) + ",
'select', true"
        print   @msg
        print   "go"
end

if @status & 8 = 8
begin
        select  @msg = "execute sp_dboption " + rtrim(@dbname) + ",
'trunc', true"
        print   @msg
        print   "go"
end

if @status & 8 = 8 or @status & 4 = 4
begin
        select  @msg = "use " + rtrim(@dbname)
        print   @msg
        print   "go"
        print   "checkpoint"
        print   "go"
end

return (0)

go

On Feb 16, 1996 17:31:17 in article <Re: Generating CREATE DATABASE

Quote:>> Could anyone who has a nifty script to automatically
>> generate CREATE DATABASE sql scripts for a given
>> SQL SERVER - please post?

I strongly suggest Platinum Tech's "Desktop DBA" or Embarcadero's "DB
Artisan" tools.  For a modest price, let them maintain the code with each
Sybase revision.
--
Don Schulze

Use Desktop DBA if you don't mind periodically rebooting your machine
because of random GPF's
JAMcKinney [3 ^ )                          

Give a man a fish and feed him for a day...Teach a man to fish, and he
will feed himself for life