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:
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