Create database and schema dynamically

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Create database and schema dynamically

Create database and schema dynamically

Rate Topic

Display Mode

Topic Options

Author

Message

Robbert Hof

Posted Wednesday, November 28, 2012 12:22 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:29 AM
Points: 1,282, Visits: 744

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

Post #1389532

Koen Verbeeck

Posted Wednesday, November 28, 2012 12:29 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 9,373, Visits: 6,470

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.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1389538

WolfgangE

Posted Wednesday, November 28, 2012 1:15 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:56 AM
Points: 74, Visits: 419

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 )

Post #1389553

Robbert Hof

Posted Wednesday, November 28, 2012 1:24 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:29 AM
Points: 1,282, Visits: 744

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.

Post #1389558

John Mitchell-245523

Posted Wednesday, November 28, 2012 1:37 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 4,428, Visits: 7,196

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

Post #1389565

WolfgangE

Posted Wednesday, November 28, 2012 1:39 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:56 AM
Points: 74, Visits: 419

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

Post #1389568

Robbert Hof

Posted Wednesday, November 28, 2012 1:44 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:29 AM
Points: 1,282, Visits: 744

weberharter you are great!!, it WORKS.

Thanks.

Post #1389571

Koen Verbeeck

Posted Wednesday, November 28, 2012 1:50 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 9,373, Visits: 6,470

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 BigGrin

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1389574

« Prev Topic | Next Topic »

Permissions