Why can't I use "create schema" in a begin/end block in SQL Management Studio?

Ensuring a Unique Schema

Before a new schema is created, check its existence to avert duplication. Add this pre-creation check to ensure idempotency of your script:

-- "Knock, Knock. Who's there? Schema. Which one?" 😆
IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'MyNewSchema'))
BEGIN
    -- "Go ahead, Schema. The database is warmed up for you!" 😉
    EXEC('CREATE SCHEMA [MyNewSchema] AUTHORIZATION [dbo]');
END


An alternative way to verify existence is using IF (SCHEMA_ID('MyNewSchema') IS NULL). When wrapped in a dynamic SQL statement, this becomes very effective for mixed environments.

Using Dynamic SQL and GO

Dynamic SQL provides flexibility to execute T-SQL commands that could be restricted in some contexts. Though you cannot use the GO statement within BEGIN...END blocks, it’s helpful for batch designation in scripts; dynamic SQL simply bypasses this need.

Effective Schema Creation Strategies

Prioritizing schema creation

To avoid potential conflicts with other database operations, prioritize schema creation, making it the first operation in your script. This strategic isolation sets up a smoother deployment process.

Dealing with special characters and reserved keywords

Handling special characters and reserved keywords means enclosing schema names in square brackets within dynamic SQL:

EXEC('CREATE SCHEMA [weird-schema-name] AUTHORIZATION [dbo]');
-- "Even names like '[weird-schema-name]' are cool in this house!" 😎


Taking note of version compatibility

Agents should note the compatibility of versions. The techniques provided here are designed for SQL Server 2008 and newer, including Management Studio R2. Older versions might have their own peculiar restrictions.

Avoiding DROP followed by CREATE

Instead of dropping and recreating schemas (unlike your ex who's been texting you a lot lately 😉), focus on ensuring the creation of a schema only if it doesn't currently exist.