Unable to create schema inside start block

Question

I am completely at a dead end. 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: The syntax is incorrect next to the keyword" SCHEMA ", but if I run only the CREATE SCHEMA command, this works.

In addition, 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 small red herring here ... Do the following to see what a "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 the query package.

To work 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

You cannot check if a circuit exists this way, you have to do it like this:

 IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Test') BEGIN execute('CREATE SCHEMA Test'); END; 

The reason is that the database engine compiles your code before running, but does not check your code logic. During compilation, the engine sees that your circuit already exists, and does not cause an error. My solution works because DB does not compile dynamic sql, so it does not know that you are trying to create an existing schema.

Answer 3

Try this option -

 IF NOT EXISTS(SELECT 1 FROM sys.schemas WHERE name = 'Test') BEGIN EXEC sys.sp_executesql N'CREATE SCHEMA Test;' END 

Answer 4

If you need to create a schema in a different database and not in the current context, you must run the following script.

  set @myScript = 'exec' + QUOTENAME (@DBName) + '..sp_executesql N``create schema MySchema' ''
 execute (@myScript)