Post reply

Create database and schema dynamically

  • 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

  • November 28, 2012 at 12:29 am

    #1562943

    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

  • November 28, 2012 at 1:15 am

    #1562958

    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 )

  • November 28, 2012 at 1:24 am

    #1562962

    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.

  • November 28, 2012 at 1:37 am

    #1562969

    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

  • November 28, 2012 at 1:39 am

    #1562972

    this one works:

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

    exec (@sql)

  • November 28, 2012 at 1:44 am

    #1562974

    weberharter you are great!!, it WORKS.

    Thanks.

  • November 28, 2012 at 1:50 am

    #1562977

    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

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

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