CODEWITHSUNDEEP
sqlsql-servert-sql
Brennan
Brennan

Reputation: 11686

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

I generated a script which creates all users and schemas for this database and when I wrap the CREATE statements with an IF EXISTS check I find that it does not allow the CREATE SCHEMA call to run in the BEGIN/END block. It complains that it is invalid syntax. Yet I can run the command on it's own. A sample of the code is below. I am using SQL Server 2008 and Management Studio R2. Why is this invalid syntax?

--DROP SCHEMA [acme]

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'acme')) 
BEGIN
    CREATE SCHEMA [acme] AUTHORIZATION [dbo]
END

Upvotes: 93

Views: 53337

Answers (5)

Raymond Reddington
Raymond Reddington

Reputation: 1837

Sometimes (always) you're not allowed to use Dynamic SQL, so using EXEC is not the best approach. Using GO statement can do things better:

USE [MyDB]
GO

IF (SCHEMA_ID('MySchema') IS NOT NULL)
BEGIN
    DROP SCHEMA [MySchema];
END

GO

CREATE SCHEMA [MySchema] AUTHORIZATION [dbo]

GO

Upvotes: 8

Mark Whitfeld
Mark Whitfeld

Reputation: 6858

Here is an even simpler solution (simpler check):

IF (SCHEMA_ID('acme') IS NULL) 
BEGIN
    EXEC ('CREATE SCHEMA [acme] AUTHORIZATION [dbo]')
END

Upvotes: 56

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

CREATE SCHEMA must be in it's own batch, so embed it inside an EXEC and you should be OK.

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'acme')) 
BEGIN
    EXEC ('CREATE SCHEMA [acme] AUTHORIZATION [dbo]')
END

Upvotes: 5

Tom H
Tom H

Reputation: 47392

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

Upvotes: 145

Martin Smith
Martin Smith

Reputation: 453707

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).

Upvotes: 11

Related Questions