CREATE SCHEMA Syntax Error

Recently while creating a script to migrate the new holding tables over to the stage and production machines I ran into a little issue.  The holding table we are using in this case houses several different sets of data for our migration project.  Tables that are used only for going from the old system to the new (which will be going away in a few months) and tables that will be used in the future while on the new system.  In order to keep these tables separated we decided put them in different schemas inside the holding database.  For this instance they will be called leaving and future.  First we are checking to see if schema already exists, which is not an issue.
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ‘Current’)
From there we need to create the schema if it doesn’t already exist, also not an issue.  A simple piece of code:
CREATE SCHEMA Current
The entire code would logically be the combination of the two.  However, if you run that, an error will occur saying there is “Incorrect syntax near the keyword ‘SCHEMA'”.  The reason for this is that the CREATE SCHEMA command has to be the first command that is given.  If you do want to check to see if a schema already exists before trying to create it there is luckily a way around that. Simply replace CREATE SCHEMA Current with EXEC ‘CREATE SCHEMA Current’ and everything will run without any errors.  The final code is listed below:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ‘Current’)
EXEC ‘CREATE SCHEMA Current’

About the author

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me