sandy_1006 avatar image
sandy_1006 asked

Array list in creating database

Hi Experts,

I have defined a array like dbname=[test,test1,test3].

How do i pass them in the query so that i can create database in one loop or array.Any hints or pointer will be very helpful with the syntax.

create database

sql server 2012development
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·

@sandy_1006 Could you please clarify what does "defined an array" mean? There are no arrays in SQL Server 2012. What would you like to accomplish? It looks like you are looking for a way to pass a delimited list of values to the script which will then create databases named after each item in such list? What about the database specifications? Do you care about those or not? What about the situation when some of those databases already exist? Should the database be dropped and re-created from scratch? Or skipped? Why do you need to have such a script? Please clarify. Thank you.

0 Likes 0 ·
sandy_1006 avatar image sandy_1006 commented ·

Thank you.By array of list i mean if i define instead of single database , Can i define multiple database.Iam working on chef where in iam writing a code how create mutiple database supplied in the create database -command.At the moment iam creating just a shell /empty databases later on i can develop /work on the questions which are very important .But at the moment just looking at the ways how i can create multiple databases names supplied.

0 Likes 0 ·

1 Answer

Oleg avatar image
Oleg answered

I am still not sure why would anyone need something like this, but here is goes. The idea is to pass the delimited list of the database names to the script which generates the create database snippets which can be then executed all at once. To split the list of delimited values in SQL Server 2016 or newer, the string_split may be used. It is not, unfortunately available in earlier versions. The string splitter by @Jeff Moden is the function to use in this case. Alternatively, considering the fact that the list of the databases to create is just that, the single list, and not the column in some table with big number of rows, the split by xml is OK to use here (this is that the script below uses). Here is the script which should work as expected. Please do not run it until it is understood what it does and how it works. To see what it does, just comment out the last line and uncomment the line printing the generated script instead. Here is the script:

-- this will be the stored procedure parameter if needed
declare @list varchar(max) = 'test,test1,test3';

-- these will be the local parameters
declare @sql nvarchar(max) = 'use master';
declare @xml xml  = '<r>' + replace(@list, ',', '</r><r>') + '</r>'
select @sql += ( 
    ';' + char(10) + 'create database ' + x.value('.[1]', 'varchar(128)')
    from @xml.nodes('r') r(x)
    for xml path('')
) + ';'
-- uncomment this line to see what the resulting script looks like
--print @sql

-- do not run until it is understood and agreed that it actually needs to run
exec sp_executesql @statement = @sql;

So when the list as in example above is used, the generated script looks like this:

use master;
create database test;
create database test1;
create database test3;

This script may be then executed by calling sp_executesql. It would be best to restate this script as the stored procedure which accepts the list of the databases to create as its single parameter. Also, the script might need to be modified depending on the requirements, i.e. what to do if the database with specified name already exists.

Hope this helps.


1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sandy_1006 avatar image sandy_1006 commented ·

Appreciate the help here and the knowledge you have shared,Thanks you!!!!

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.