Blog Pages

Incorrect syntax near the keyword 'SCHEMA'

If you try to execute:
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'NewSchemaName')
       CREATE SCHEMA [NewSchemaName] AUTHORIZATION [dbo]
GO

You will get this error message:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SCHEMA'.

And it although this command will success:
CREATE SCHEMA [NewSchemaName] AUTHORIZATION [dbo]

So why we’ve got the error?
Because schema creation must be the first command in a batch.

A solution:
Run it as dynamic SQL:
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'NewSchemaName')
       EXEC sp_executesql N'CREATE SCHEMA [NewSchemaName] AUTHORIZATION [dbo]'
GO

No comments:

Post a Comment