How to check if schema exists in SQL server database before creating

When writing SQL scripts it's always a good idea to write them with the thought in mind that we will have to end up re-running the script multiple times. You also need to take into account that an object may or may not exist, andwill need to either create or recreate it on some conditions.

In my particular case I need to check if schema exists in SQL server database and create a new Schema within a database. i.e. Archive . I tried a simple approach similar too.

How to check if schema exists on sql server

It turns out that a CREATE SCHEMA needs to be a first command in a batch and cannot be wrapped in an if statement. So a little work around is needed.

IF NOT EXISTS (
SELECT  schema_name
FROM    information_schema.schemata
WHERE   schema_name = 'ARCHIVE' ) -- ARCHIVE is the name of the schema I wanted to check for
 
BEGIN
EXEC sp_executesql N'CREATE SCHEMA ARCHIVE'   --ARCHIVE is the schema I want to create
END
GO

In my particular case that worked a treat. I was able to develop my scripts that I needed and check if my specific schemas were available

What doesn't work

IF NOT EXISTS (
SELECT  schema_name
FROM    information_schema.schemata
WHERE   schema_name = 'ARCHIVE' ) -- ARCHIVE is the name of the schema I wanted to check for
 
BEGIN
CREATE SCHEMA [ARCHIVE] AUTHORIZATION [dbo]
END

When I tried running the script MS SQL simply responded

Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'SCHEMA'.

About
Latest Posts

Gary Woodfine

Technical Director at threenine.co.uk

Gary is Technical Director at threenine.co.uk, an independent software vendor specialising in IoT, Field Service and associated managed services,enabling customers to be efficient, productive, secure and scale-able.