Why can't I use "create schema" in a begin/end block in SQL Management Studio?
Solution 1
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
Solution 2
Here is an even simpler solution (simpler check):
IF (SCHEMA_ID('acme') IS NULL)
BEGIN
EXEC ('CREATE SCHEMA [acme] AUTHORIZATION [dbo]')
END
Solution 3
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).
Solution 4
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
Solution 5
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
Brennan
Updated on July 05, 2022Comments
-
Brennan about 2 years
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
-
Brennan about 13 yearsI've cleared that I can wrap the CREATE SCHEMA statement with EXECUTE to allow the statement to work. See EXECUTE('CREATE SCHEMA [acme] AUTHORIZATION [dbo]') msdn.microsoft.com/en-us/library/ms189462.aspx
-
Chris B. Behrens about 8 yearsThis is horseshit...surely we're not all cluttering our code with pointless EXEC statements?
-
Ian Kemp over 4 yearsIf only
create schema
supported theif exists
predicate... ;_;
-
-
marko982 about 4 yearsIf you already have tables for that schema then you cannot drop it without dropping the tables (or other object).