scripting database creation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

scripting database creation

Author

Topic

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-01-12 : 21:37:57

Hi Champs

Got a customer who wants to script his database creation (lots of little ones) ideally based on parameters.

The database creation command doesn't appear to like variables, and so this would appear to be a problem. Eg

CREATE DATABASE @DBName
ON 
( NAME = @DBName + '_dat',
   FILENAME = @DBFile,
   SIZE = @DBSize)
LOG ON
( NAME = @DNName + '_log',
   FILENAME = @DBLog,
   SIZE = @DBSize)
GO

Is there any way I can do this?

(be gentle with me, I'm not used to the big, scary admin forum)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-01-12 : 22:03:00

You could use dynamic SQL for this.

Declare @sql nvarchar(4000),
@dbname nvarchar(50),
@dbfile nvarchar(50),
@dblog nvarchar(50),
@dbsize int

SELECT
@dbname = 'DynamicTest',
@dbfile = 'foo',
@dblog = 'foo',
@DBsize = 10

SELECT @sql = 'CREATE DATABASE ' + @DBName + '
ON
( NAME = ' + @DBName + '_dat,
FILENAME = ' + @DBFile + ',
SIZE = ' + Cast(@DBSize as nvarchar(10)) + ')
LOG ON
( NAME = ' + @DBName + '_log ,
FILENAME = ' + @DBLog + ',
SIZE = ' + Cast(@DBSize as nvarchar(10)) + ')'

Execute( @sql )

Damian

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-01-12 : 22:16:39

There once was a Yak from SA
Who was scared of posting as DBA
He needed a scriptlet
That wasn't explicit
And dynamic sql saved the day

Damian

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-01-12 : 22:23:21

Touché

No really Damian, that really was grand - both responses!

A Yak from Newtown in the east
Offered free help to all, man and beast
although they were tentative
he was not argumentative
but gracious even unto the least....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources