CODEWITHSUNDEEP
sqlsql-server
mzedeler
mzedeler

Reputation: 4369

Can't create schema inside begin block

I'm completely stumped. I have the following block:

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Test')
BEGIN
    CREATE SCHEMA Test;
END;

If I run this against our SQL Server 2008, I get "Msg 156, Level 15, State 1, Line 3: Incorrect syntax near the keyword 'SCHEMA'" but if I run just the CREATE SCHEMA command alone, it works.

Also, this works:

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Test')
BEGIN
    PRINT 'CREATE GOES HERE';
END;

What am I doing wrong?

Upvotes: 13

Views: 18685

Answers (4)

Will Wu
Will Wu

Reputation: 631

If you need to create schema in another database rather than current context, you should run following script.

set @myScript = 'exec '+ QUOTENAME(@DBName) + '..sp_executesql N''create schema MySchema'''
execute(@myScript)

Upvotes: 11

Devart
Devart

Reputation: 122032

Try this one -

IF NOT EXISTS(SELECT 1 FROM sys.schemas WHERE name = 'Test')
BEGIN
    EXEC sys.sp_executesql N'CREATE SCHEMA Test;'
END

Upvotes: 5

Robert
Robert

Reputation: 25763

You can't verify if schema existents that way, you should do it that way:

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Test')
BEGIN
    execute('CREATE SCHEMA Test');
END;

The reason is because DB engine compiles your code before run it but not validates your code's logic. During compiling engine sees that your schema already exists a than rise an error. My solution works because DB doesn't compile dynamic sql so it doesn't know you are trying create existing schema.

Upvotes: 4

gvee
gvee

Reputation: 17171

The error message is a bit of a red herring here.... Execute the following to see what the "real" error is:

SELECT * FROM sys.schemas
CREATE SCHEMA Test

Msg 111, Level 15, State 1, Line 2

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

To get around this problem you can use the EXEC function:

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Test')
  BEGIN
    EXEC ('CREATE SCHEMA Test;');
  END;

Upvotes: 32

Related Questions