Creating a lot number of databases using T-SQL query

Probably some time you will require create a lot number of database and you could note that create database [database name] does not allow a string as database name; then we could use sp_executesql for this task; this store procedure receives a string and execute this sting, and we could modify the string each iteration to create databases with different names. see below sample

 

declare @i as int -- @i will be our counter

declare @j as int -- @j will be our limit

declare @t as nchar(30) -- @t will be our text value, if you want concatenate this value to any text

 

set @i = 1            -- we put 1 in our first value

set @j = 5000       -- we put our limit in @j

 

WHILE @j >= @i

Begin

      set @t = @i

      set @t = 'create database db'+@t --this is the query to create the database and we add the counter

      exec sp_executesql @t --how us this sp to execute the query

      set @i = @i + 1

End