At my currently company, we aim to make our database deployment scripts so that they can be re-run any number of times without generating errors. So that means we either drop and recreate objects, or we check to see if objects already exist before trying to create them. For most objects, that’s easy to do. However, when we create a new schema, things are a little tricky. We can’t drop and recreate a schema if it contains any objects, so we need to check for existence and then create it. However, A CREATE SCHEMA statement has to be the only statement in a batch, so we can’t include a CREATE in an IF statement.
We end up checking if the schema exists, and then run the CREATE command in an EXECUTE command, where it will be the only statement in the batch.
IF SCHEMA_ID('TestSchema') IS NULL EXEC ('CREATE SCHEMA [TestSchema] AUTHORIZATION dbo'); GO