need to create 500 sql server DBs

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

need to create 500 sql server DBs

Author

Topic

pcoates
Starting Member

2 Posts

Posted - 2010-05-04 : 18:08:00

Hello,

I have been tasked w/creating 500 sql server DBs, and I am not familiar w/scripting w/loops but I absolutely dread creating a file w/500 'create database' statements. Here is what I have so far, but the syntax of the 'create database' statement is wrong. Can/will someone help - please?!? Thanks in advance. Pcoates

DECLARE @str VARCHAR(10)
DECLARE @index INT

SET @str = 'DB'
SET @index = 000

WHILE @index < 500
BEGIN
create database @str@index
set @index = @index + 1
END

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-04 : 18:18:00

[code]DECLARE @str VARCHAR(10)
DECLARE @index INT
DECLARE @SQL VARCHAR(100)

SET @str = 'DB'
SET @index = 0

WHILE @index < 500
BEGIN
SET @SQL = 'create database ' + @str + CASE
WHEN @index < 10 THEN '00'
WHEN @index < 100 THEN '0'
ELSE ''
END + CAST(@index as VARCHAR)
EXEC(@SQL)

set @index = @index + 1
END[/code]

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-04 : 23:24:52

I sure hope you don't plan on putting 500 databases onto one SQL instance. The maximum should be around 50 unless they are super tiny. I wouldn't put more than 100 databases on any SQL instance. Just imagine how long the database maintenance jobs will take.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pcoates
Starting Member

2 Posts

Posted - 2010-05-05 : 00:10:58

Awesome! Thanks a million DBA in the making!!

THe DBs will be very small and spread across multiple instances. I am performing hardware load/performance testing and have 500 DBs as my benchmark. Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources