Can't create schema inside begin block

Question

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?

Answer 1

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;

Answer 2

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)