Blog

SQL: Create Schema If Not Exists

When deploying code I try to create my scripts so they can be rerun without an error being thrown (e.g. "object already exists").  There's some fancy word for this that I used to try and use so as to appear ostentatious but it turns out people found me ostentatious even when I didn't use the word so I concluded I didn't need to use it anymore and erased it from my memory.

Alright, so if you are creating a new schema and you want to perform a check to create the schema only if it doesn't already exist, a la the standard "IF...EXISTS" pattern, you'd think you'd just handle it the same as everywhere else right?  Something like this:

IF NOT EXISTS (
SELECT  schema_name
FROM    information_schema.schemata
WHERE   schema_name = 'SampleSchema' ) 

BEGIN
CREATE SCHEMA SampleSchema
END

Wrong!  You actually end with a vague:

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

Check your syntax as much as you'd like and you won't find the problem.  The catch is: create schema must be the first command in a batch.  Bummer!

What will we do? Give up?  Make our deploy script so it is non-rerunnable? (hmm, that fancy word would have been handy right then).  Never fear!  You just need to make the create schema the first command in the batch.

Wait, what?  That's right, get out of that box you're thinking in!  Check it out!

IF NOT EXISTS (
SELECT  schema_name
FROM    information_schema.schemata
WHERE   schema_name = 'SampleSchema' ) 

BEGIN
EXEC sp_executesql N'CREATE SCHEMA SampleSchema'
END

Wham! Bam! Thank you ma'am!  Simple as that.

Create schema if it doesn't exist? Wham Bam! You got it! (now get me some coffee!)

Thanks to Andy Whitlock for the great pic.  Check out his blog: Now In Colour

 

 

Tags: , , , , , , , , , ,

No comments yet.

Leave a Reply