Create database and schema dynamically

Question

Create database and schema dynamically

Robbert Hof

SSCrazy

Points: 2778
More actions
November 28, 2012 at 12:22 am

#266629

I want to make a Stored Procedure to create a database with a dynamic name.
In that SP I have to create a new schema in that new database as well.
create proc CREATE_LOAD_DB @load_year int, @load_month int
as
declare @dbname sysname
declare @sql nvarchar(max)
set @dbname = 'DB_' + CAST(@load_year as varchar(4)) + CAST(@load_month as varchar(2))
set @sql = 'create database ' + QUOTENAME(@dbname)
exec (@sql)
How to create a schema in this new database within the SP?
thanks,
Robbert

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

I don't think you can.

You can't use the "USE database" statement in a stored procedure and you cannot qualify the schema with a database name in the CREATE SCHEMA statement.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

WolfgangE SSCertifiable Points: 6328 More actions · Answer 2

It should work if you include the USE-Statement in the dynamic SQL-String for creating the schema.

set @sql = 'use ' + QUOTENAME(@dbname) + ' create schema MyNewSchema';

exec ( @sql )

Robbert Hof SSCrazy Points: 2778 More actions · Answer 3

I tried that, giving this error:

Msg 111, Level 15, State 1, Line 1

'CREATE SCHEMA' must be the first statement in a query batch.

A 'GO' in between the USE and CREATE is also not working.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

I don't know if this would work for you, but you could create the schema in the model database. Then it would appear automatically in all new databases.

John

WolfgangE SSCertifiable Points: 6328 More actions · Answer 5

this one works:

set @sql = 'exec '+ QUOTENAME(@dbname) + '..sp_executesql N''create schema MyNewSchema'''

exec (@sql)

Robbert Hof SSCrazy Points: 2778 More actions · Answer 6

Robbert Hof

SSCrazy

Points: 2778

November 28, 2012 at 1:44 am

#1562974

weberharter you are great!!, it WORKS.

Thanks.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

weberharter (11/28/2012)
this one works:
set @sql = 'exec '+ QUOTENAME(@dbname) + '..sp_executesql N''create schema MyNewSchema'''
exec (@sql)

Congrats for proving me wrong 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP