{"id":9127,"date":"2022-07-28T06:46:00","date_gmt":"2022-07-28T04:46:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=9127"},"modified":"2022-07-29T15:12:36","modified_gmt":"2022-07-29T13:12:36","slug":"check-if-object-exists-with-ssms","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/","title":{"rendered":"Check if object exists with SSMS"},"content":{"rendered":"\n

How to add or remove a script to check if an object exists in SQL creation scripts generated with SQL Server Management Studio (SSMS)? To add dynamically the existence check before generating the create statement for you SQL Objects\u00a0like tables, views, \u00a0functions or stored procedures, it’s easy. Simply activate this\u00a0SSMS option to check object existence in your SQL Server Management Studio software.<\/p>\n\n\n\n

For example, when developing SQL Server Stored procedures, you need to check their existence first, to avoid this error messages. It’s very useful when you have to change big stored procedures and deploying many times on the server for tests purposes.<\/p>\n\n\n\n

Automatically add a script to check if an object exists with SQL Server Management Studio (SSMS)<\/h2>\n\n\n\n

For a Table, a View a Stored Procedure or a Function, the message is similar:<\/p>\n\n\n\n

Msg 2714, Level 16, State 6, Line 1
There is already an object named ‘Your-Table’ in the database.<\/em><\/p>\n\n\n\n

To do so, you just need to activate this SSMS option to check object existence:<\/p>\n\n\n\n

  1. From the top left menu, click “Tools > Options”<\/strong>.<\/li>
  2. In the “SQL Server Object Explorer”<\/strong> section, chose “Scripting”.<\/strong><\/li>
  3. Then set the “Check for object existence<\/strong>” option to “True”.<\/strong><\/li><\/ol>\n\n\n
    \n
    \"\"<\/figure><\/div>\n\n\n

    Then it’s adding this existence check before the code creation of your procedure. To generate the code, right click on any object, for example, a stored procedure and select: <\/p>\n\n\n\n

    1. Script Stored Procedure AS<\/strong><\/li>
    2. Then CREATE TO<\/strong><\/li>
    3. And New Query Editor Window <\/strong><\/li><\/ol>\n\n\n\n

      The script automatically generated by SSMS looks like this one: <\/p>\n\n\n\n

      IF NOT EXISTS (\n\tSELECT * FROM sys.objects \n\tWHERE object_id = OBJECT_ID(N'[dbo].[YourStoredProcedure]') \n\t\tAND type in (N'P', N'PC'))\nBEGIN\nEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[YourStoredProcedure] AS SELECT 1 as [One]' \nEND\nGO\n\nALTER PROCEDURE [dbo].YourStoredProcedure \nAS \nBEGIN\nSELECT GETDATE() as [CurrentDate]\nEND \nGO\n<\/pre>\n\n\n\n
      Note<\/strong><\/span>: In case the object does not exists, it’s simply creating an empty stored procedure. Indeed, and simply using an alter statement on the same object, in order to limit the length of the script<\/a>.<\/p>\n\n\n
      \n
      \"\"<\/figure><\/div>\n\n\n
      It’s very useful to use error free scripts for a stored procedure. For example because it is not needed to handle any data content or data structure, like for tables for example. <\/p>\n\n\n\n
      Indeed for tables and especially sensitive or business critical data, it is mandatory to manage a migration script, generate and backup the table contrent before dropping and recreating a SQL table<\/a><\/strong>.<\/p>\n\n\n\n
      So it is definitely a good practice for SQL projects to check if object exists in the SQL database. So generate the scripts with SSMS or manually and use them. In some cases, it is useful to stop the script if the table already exists to prevent dropping a table by mistake. <\/p>\n\n\n\n
      This section of the blog is about the common SQL Server Errors like the SQL Server arithmetic overflow error<\/a><\/strong> for example.<\/p>\n","protected":false},"excerpt":{"rendered":"
      How to add or remove a script to check if an object exists in SQL creation scripts generated with SQL Server Management Studio (SSMS)? To add dynamically the existence check before generating the create statement for you SQL Objects\u00a0like …<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":5525,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[494],"tags":[],"class_list":{"0":"post-9127","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-ssms"},"yoast_head":"\nCheck if object exists with SSMS - SSMS<\/title>\n<meta name=\"description\" content=\"Add the option to automatically check if the object exists with SSMS, the object existence add a test in all your T-SQL generated scripts.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Check if object exists with SSMS\" \/>\n<meta property=\"og:description\" content=\"Add the option to automatically check if the object exists with SSMS, the object existence add a test in all your T-SQL generated scripts.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-07-28T04:46:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-07-29T13:12:36+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/coffee-631767_1920.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"Check if object exists with SSMS\",\"datePublished\":\"2022-07-28T04:46:00+00:00\",\"dateModified\":\"2022-07-29T13:12:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/\"},\"wordCount\":426,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/coffee-631767_1920.jpg\",\"articleSection\":[\"SSMS\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/\",\"url\":\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/\",\"name\":\"Check if object exists with SSMS - SSMS\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/coffee-631767_1920.jpg\",\"datePublished\":\"2022-07-28T04:46:00+00:00\",\"dateModified\":\"2022-07-29T13:12:36+00:00\",\"description\":\"Add the option to automatically check if the object exists with SSMS, the object existence add a test in all your T-SQL generated scripts.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/coffee-631767_1920.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/coffee-631767_1920.jpg\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Check if object exists with SSMS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Check if object exists with SSMS - SSMS","description":"Add the option to automatically check if the object exists with SSMS, the object existence add a test in all your T-SQL generated scripts.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/","og_locale":"en_US","og_type":"article","og_title":"Check if object exists with SSMS","og_description":"Add the option to automatically check if the object exists with SSMS, the object existence add a test in all your T-SQL generated scripts.","og_url":"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2022-07-28T04:46:00+00:00","article_modified_time":"2022-07-29T13:12:36+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/coffee-631767_1920.jpg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"Check if object exists with SSMS","datePublished":"2022-07-28T04:46:00+00:00","dateModified":"2022-07-29T13:12:36+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/"},"wordCount":426,"commentCount":0,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/coffee-631767_1920.jpg","articleSection":["SSMS"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/","url":"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/","name":"Check if object exists with SSMS - SSMS","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/coffee-631767_1920.jpg","datePublished":"2022-07-28T04:46:00+00:00","dateModified":"2022-07-29T13:12:36+00:00","description":"Add the option to automatically check if the object exists with SSMS, the object existence add a test in all your T-SQL generated scripts.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/coffee-631767_1920.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/coffee-631767_1920.jpg","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/ssms\/check-if-object-exists-with-ssms\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"Check if object exists with SSMS"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}}]}},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/9127","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=9127"}],"version-history":[{"count":0,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/9127\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/5525"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=9127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=9127"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=9127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}