How to write a stored procedure that will create 20 databases

Question

Hi

Can anybody tell me how to write a stored procedure that creates 20 databases in c:\test\

if i put a storedprocedure for database creation in a loop then

if i create a first db as test again loop will calls sp.but it shows test alredy there. I don't know how to concatenate a variable to this so that i wil create test1 test2 test3 ....test20

How can i do this Any idea..I have no idea about concatenation I am new to SQL Server sp.

Answer 1

I strongly suggest against doing this in anything that would be considered production... But you COULD, if you were so inclined, do an sp_execsql('create database xxx') statement to do your database creation statements.

So... Why do you need to do this again? ;-)

Answer 2

Use TRANSACT-SQL & IF EXISTS, to test if a database already exist.

Answer 3

CREATE PROC dbo.Foo
AS
SET NOCOUNT ON

DECLARE @i int, @stem varchar(20), @db varchar(20), @sql varchar(1000)

SELECT @i = 1, @stem = 'Test'

WHILE @i < 20
BEGIN
    SELECT @db = @stem + CAST(@i AS varchar(2))
    IF DB_ID(@db) IS NULL
    SELECT @SQL = 'CREATE DATABASE ' + @db + 
                ' ON (NAME = ' + @db + '_Data, FILENAME = ''C:\Test\' + @db + '.mdf'')' +
                ' LOG ON (NAME = ' + @db + '_Log, FILENAME = ''C:\Test\' + @db + '.mdf'')'

    EXEC(@sql)
    SELECT @i = @i + 1
END
GO