Today, I could not remember quickly how we check for the existence of schema in a database. This is not something I just learned, but something I forgot over the course of time. Since I don't want to rummage through my scripts or hit the web and to help my friends in the same boat :) I am creating this tip.

Generally, you do not want to drop and recreate the schemas and tables when updating the prod/staging database for new releases. To avoid this we need to check if the schema/table does not exist and then create it.

With schema, although I had the correct sql, I was noticing the error message in the query window.

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

But this was returning an error message "Incorrect syntax near the keyword 'schema'." and in the query window I noticed the message "CREATE SCHEMA statement must be the only statement in the batch"

Work around is to execute the CREATE SCHEMA statement in an EXECUTE

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = '<name>')
BEGIN
    EXECUTE( 'CREATE SCHEMA <name>' );
END