Post reply

Simple script - Does this schema exist? If not, create it.

  • March 30, 2009 at 12:27 pm

    #132749

    I want a script that will create a schema if it does not exist.

    Example.

    IF NOT EXISTS (SELECT NAME FROM Sys.Schemas WHERE NAME = 'TEST')

    BEGIN

    CREATE SCHEMA TEST

    END

    This is the error I get;

    Msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword 'SCHEMA'.

    Any ideas? This seems so simple, but is driving me nuts.

    Thanks,

    Chris

  • March 30, 2009 at 12:44 pm

    #968576

    Books Online says "This statement must be executed as a separate batch." so I'm guessing you can't do what you're hoping for.

    This would work however:

    DECLARE @sql NVARCHAR(255)

    SET @sql = 'create schema test'

    IF NOT EXISTS (SELECT NAME FROM Sys.Schemas WHERE NAME = 'TEST')

    BEGIN

    EXEC sp_executesql @sql

    END

  • March 30, 2009 at 12:48 pm

    #968578

    Thanks! That worked. It's funny, i tried the exact same thing with exception to one item. I didn't have sp_executesql between exec and @sql, so it was trying to execute it as a stored procedure.

    Thanks,

    Chris

Viewing 3 posts - 1 through 2 (of 2 total)

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