{"id":299,"date":"2012-08-01T11:57:27","date_gmt":"2012-08-01T18:57:27","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=299"},"modified":"2012-08-01T12:14:11","modified_gmt":"2012-08-01T19:14:11","slug":"sql-create-schema-if-not-exists","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2012\/08\/sql-create-schema-if-not-exists\/","title":{"rendered":"SQL: Create Schema If Not Exists"},"content":{"rendered":"<p>When deploying code I try to create my scripts so they can be rerun without an error being thrown (e.g. \"object already exists\").\u00a0 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.<\/p>\n<p>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?\u00a0 Something like this:<!--more--><\/p>\n<pre>IF NOT EXISTS (\r\nSELECT\u00a0 schema_name\r\nFROM\u00a0\u00a0\u00a0 information_schema.schemata\r\nWHERE\u00a0\u00a0 schema_name = 'SampleSchema' ) \r\n\r\nBEGIN\r\nCREATE SCHEMA SampleSchema\r\nEND<\/pre>\n<p>Wrong!\u00a0 You actually end with a vague:<\/p>\n<pre>Msg 156, Level 15, State 1, Line 5\r\nIncorrect syntax near the keyword 'SCHEMA'.<\/pre>\n<p>Check your syntax as much as you'd like and you won't find the problem.\u00a0 The catch is: create schema must be the first command in a batch.\u00a0 Bummer!<\/p>\n<p>What will we do? Give up?\u00a0 Make our deploy script so it is non-rerunnable? (hmm, that fancy word would have been handy right then).\u00a0 Never fear!\u00a0 You just need to make the create schema the first command in the batch.<\/p>\n<p>Wait, what?\u00a0 That's right, get out of that box you're thinking in!\u00a0 Check it out!<\/p>\n<pre>IF NOT EXISTS (\r\nSELECT\u00a0 schema_name\r\nFROM\u00a0\u00a0\u00a0 information_schema.schemata\r\nWHERE\u00a0\u00a0 schema_name = 'SampleSchema' ) \r\n\r\nBEGIN\r\nEXEC sp_executesql N'CREATE SCHEMA SampleSchema'\r\nEND<\/pre>\n<p>Wham! Bam! Thank you ma'am!\u00a0 Simple as that.<\/p>\n<figure id=\"attachment_300\" aria-describedby=\"caption-attachment-300\" style=\"width: 640px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-300 \" title=\"create schema if not exists\" src=\"http:\/\/www.sqldbpros.com\/wordpress\/wp-content\/uploads\/2012\/08\/createschemaifnotexists.jpg\" alt=\"\" width=\"640\" height=\"378\" srcset=\"http:\/\/sqldbpros.com\/wordpress\/wp-content\/uploads\/2012\/08\/createschemaifnotexists.jpg 640w, http:\/\/sqldbpros.com\/wordpress\/wp-content\/uploads\/2012\/08\/createschemaifnotexists-300x177.jpg 300w\" sizes=\"auto, (max-width: 640px) 100vw, 640px\" \/><figcaption id=\"caption-attachment-300\" class=\"wp-caption-text\">Create schema if it doesn't exist? Wham Bam! You got it! (now get me some coffee!)<\/figcaption><\/figure>\n<address><em>Thanks to Andy Whitlock for the great pic.\u00a0 Check out his blog: <a href=\"http:\/\/nowincolour.com\/2010\/08\/thank-you-maam\">Now In Colour<\/a><\/em><\/address>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When deploying code I try to create my scripts so they can be rerun without an error being thrown (e.g. \"object already exists\").\u00a0 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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43,3,57],"tags":[35,34,70,10,6,9,12,11,173,5,69],"class_list":["post-299","post","type-post","status-publish","format-standard","hentry","category-microsoft-sql-server","category-scripts","category-t-sql","tag-best-practice","tag-development","tag-if-exists","tag-ms-sql","tag-script","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-t-sql","tag-tips","tag-wham-bam"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/299","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=299"}],"version-history":[{"count":7,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/299\/revisions"}],"predecessor-version":[{"id":302,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/299\/revisions\/302"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=299"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=299"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=299"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}