0

I want to create multiple database at one go almost 120 in MS SQL. I have tried by using script but that is not working.

--Create Variables
Declare @DataFilePath VARCHAR(100)
Declare @LogFilePath VARCHAR(100)
Declare @SubPartDBName VARCHAR(100)
Declare @StartCnt int
Declare @MaxDBCnt int

--Set the Variable Values, @MaxDBCnt is Number of Databases you want to Create
SET @StartCnt=1
SET @MaxDBCnt=2

--Provide the Data File Path And Log File Path
SET @DataFilePath='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\'
SET @LogFilePath='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\'
--Chose the First part of your DB name, Let's say TEST is chosen then Databae will be created Test1,Test2....Test100
SET @SubPartDBName='test'


--Create Databases
While ( @startCnt<@MaxDBCnt)
BEGIN

Print CAst(@startCnt AS VARCHAR(100))
DECLARE @DBFullName VARCHAR(500) =@SubPartDBName+CAST(@StartCnt AS VARCHAR(10))
DECLARE @SQL NVARCHAR(MAX)
SET @SQL= 'CREATE DATABASE ['+@DBFullName+']

 ON 
( NAME = N'''+@DBFullName+''', FILENAME = N'''+@DataFilePath+@DBFullName+'.mdf'' ,
 SIZE = 4096KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'''+@DBFullName+'_log'', FILENAME = N'''+@LogFilePath+@DBFullName+'_log.ldf'' ,
 SIZE = 1024KB , FILEGROWTH = 10%)'
SET @startCnt=@startCnt+1
Print @SQL
Execute (@SQL)
END

Please help me to create tis for project work testing

Sadamh Hussain
  • 1
  • 2
  • 6
  • 1
    Why do you want to create 120 databases in one go in the first place? Smells like an [XY Problem](http://xyproblem.info). – Thom A Jul 12 '21 at 11:57
  • 2
    Separate note, syntax like `'['+@DBFullName+']'` is **not** safe from injection. Use `QUOTENAME` to properly delimit identify your dynamic object names. – Thom A Jul 12 '21 at 11:58
  • 1
    If you want to write / use dynamic sql, then you need to debug it yourself; "Not working" is never a useful comment to make. How do you know it is not working? Does it generate an error? What is the actual statement that is causing your error? And yes - 120 empty databases sounds like utter nonsense to me. – SMor Jul 12 '21 at 12:04

0 Answers0