100 Databases - 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

General SQL Server Forums

New to SQL Server Programming

100 Databases

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-16 : 14:24:34

Hello,
Can someone show me the TSQL I need to write in order to create 100 SQL databases in one go.
I want them to be called DB1, DB2, DB3, DB4...etc etc

I know how to create one database;
---------------------------------------------------
USE MASTER
GO
CREATE DATABASE DB1
ON PRIMARY
(
NAME = DB1_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DB1.mdf',
SIZE=4,
MAXSIZE=10,
FILEGROWTH=1
)
-------------------------
But I don't want to do this 100 times,

Thanks in advance
ICW

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-16 : 16:05:58

Did u try creating the whole thing as a string with changing DB name, in a loop and executing it

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-16 : 16:13:43

I can see that I need to create while loop but I haven't the first idea where to start....any clues?
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-16 : 16:20:04

You wil need to use dynamic SQL with a loop. So you will need to build the CREATE DATABASE statement and execute it using EXEC. Here's a start (... indicates where you'll need to finish up the code):


DECLARE @i int, @SQL varchar(7000)

SET @i = 1

WHILE @i <= 100
BEGIN
	SET @SQL = 'CREATE DATABASE DB' + CONVERT(varchar(3), @i) ...
	EXEC (@SQL)
	SET @i = @i + 1
END

Tara Kizer
aka tduggan

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-16 : 16:23:38

Brill, thanks a lot for the start!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-16 : 16:26:18

Is no one else curious why icw wants to create 100 databases? Sounds like it will be a good story.

Be One with the Optimizer
TG

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-16 : 16:32:32

WHere did I go wrong?
---------------------------
DECLARE @i int, @SQL varchar(7000)

SET @i = 1

WHILE @i <= 100
BEGIN
SET @SQL = 'CREATE DATABASE DB' + CONVERT(varchar(3), @i)
EXEC
USE MASTER
GO
CREATE DATABASE 'DB' + CONVERT(varchar(3), @i)
ON PRIMARY
(
NAME = 'DB' + CONVERT(varchar(3), @i)+'_dat',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DB' + CONVERT(varchar(3), @i)+'.mdf',
SIZE=4,
MAXSIZE=10,
FILEGROWTH=1
)

SET @i = @i + 1
END
------------------------------------
Is it miles away or am i getting the right idea?

thanks again

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-16 : 16:55:14

try this (just to print out the code) If you like it you can paste it into a new window or save to a file or whatever:


print '
use master
' +  + char(103) + char(111) 


DECLARE @i int, @SQL varchar(7000)

SET @i = 1

WHILE @i <= 100
BEGIN
	SET @SQL = 'CREATE DATABASE DB' + CONVERT(varchar(3), @i) + '
	ON PRIMARY
	(
	NAME = ''DB' + CONVERT(varchar(3), @i)+'_dat'',
	FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL\Data\DB' + CONVERT(varchar(3), @i)+'.mdf'',
	SIZE=4,
	MAXSIZE=10,
	FILEGROWTH=1
	)
' + char(103) + char(111) 

	print @sql
	
	SET @i = @i + 1
END

Be One with the Optimizer
TG

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-16 : 17:04:53

You are wonderful, THANKS VERY MUCH!!!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-16 : 17:08:16

No problem...but Tara's the genius.

>>WHere did I go wrong?
notice that embedded single quotes (within a quoted string) need to be doubled up (2 single quotes). Also, as Tara illustrated, to exec the string rather than print it use:
exec(@sql)

I used char(103) + char(111) to represent a "go" key word because the word "go" would have been interpereted by sql server as a sql batch terminater (even though it's inside a string)

char(103) = 'g'
char(111) = 'o'

EDIT:
so does this mean you'll explain why you want 100 databases?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-16 : 17:15:12

quote:
I used char(103) + char(111) to represent a "go" key word because the word "go" would have been interpereted by sql server as a sql batch terminater (even though it's inside a string)

woops I was wrong about that. This works fine. I don't know why I thought you could't do this:

print '
use master
go'

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-16 : 17:51:41

I don't think you need the GOs at all in this script.

Tara Kizer
aka tduggan

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-16 : 21:36:14

100 databases, is the server still up?
i'm curious the specs of the server involved plus the database sizes

also, this will be a monster to manage eh? or is it simple an exercise to prove the power of dsql?

--------------------
keeping it simple...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-16 : 22:02:44

Put me in the "I doubt that this is really something that is a good idea" camp.

Any chance you'd care to explain to us why you are doing this?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-16 : 22:43:58

I guessing this is some kind of one database per user design.

Should be a lot of fun to setup replication.

CODO ERGO SUM

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-17 : 00:21:36

Hello Again,
When I said "YOU are wonderful" I was referring to all of you lovely people who helped out an unsure beginner. I really appreciate it.

The 100 databases is for a telemarketing company. They have a database for each of their clients. i.e. their clients send them client lists every month or so for calling. The telemarketing company don't want to mix it all up, especially as their could potentially be duplicates. I know that by flagging record owners that the duplication of contacts wouldn't normally be a problem, but this front end app links incoming emails for the contacts based on email addresses and duplicate email addresses would be a real problem. There is only actually a requiremnet for about 80 databases, but i thought it would be an intersting test to see if we could make 100.

kid_on_the_block
Posting Yak Master

172 Posts

Posted - 2006-03-17 : 00:57:19

You could have had 100 tables !!!! right ????

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-17 : 01:00:29

quote:
Originally posted by kid_on_the_block

You could have had 100 tables !!!! right ????

That's still bad design.

Tara Kizer
aka tduggan

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2006-03-17 : 01:02:43

Unfortunately the front end app (GoldMine) requires a database for each contact set, as each database not only has the contact details table, it also has history tables, and other tables linked to the contact details table. They all have to be called exactly the same thing as well. FOr example the contact details table MUST be called CONTACT1 otherwise the app won't read it, likewise the history table MUST be called CONTHIST etc

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-03-17 : 01:04:50

Seems like a pretty crappy application to me.

kid_on_the_block
Posting Yak Master

172 Posts

Posted - 2006-03-17 : 01:06:30

hey tara why would the 100 tables be a bad design please explain

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources