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.
