Why can't I use "create schema" in a begin/end block in SQL Management Studio?
Schema creations must be the only statement in a batch. One way to get around it is like so:
IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'acme'))
BEGIN
EXEC ('CREATE SCHEMA [acme] AUTHORIZATION [dbo]')
END
Here is an even simpler solution (simpler check):
IF (SCHEMA_ID('acme') IS NULL)
BEGIN
EXEC ('CREATE SCHEMA [acme] AUTHORIZATION [dbo]')
END
It needs to be in its own batch. You can wrap it in EXEC('')
EXEC('CREATE SCHEMA [acme] AUTHORIZATION [dbo]')
I believe the reason for the requirement is something to do with an older version of the CREATE SCHEMA
syntax introduced in version 6.5 (at least that's what it says here).