{"id":7252,"date":"2022-04-13T06:11:00","date_gmt":"2022-04-13T04:11:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=7252"},"modified":"2022-05-12T16:07:42","modified_gmt":"2022-05-12T14:07:42","slug":"check-if-table-exists-in-sql-server","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/","title":{"rendered":"Check if table exists in SQL Server"},"content":{"rendered":"\n<p><strong>How to Check if table exists in SQL Server before to delete it ? How to drop it only if the existence check shows it already exists in the database to avoid errors, using the DROP TABLE keyword. Indeed, when running long SQL scripts or running scripts twice, it is much better to test the table existence.<\/strong><\/p>\n\n\n\n<p>Indeed, we highly recommend testing the table existence to avoid the following error message. To drop a SQL Server table without error, simply use this code.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-check-if-a-sql-server-table-exists-before-delete\">How to check if a SQL Server table exists before delete?<\/h2>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"h-how-to-avoid-and-fix-this-sql-server-error\">How to avoid and fix this SQL Server Error?<\/h4>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"h-msg-3701-level-11-state-5-line-1cannot-drop-the-table-sales-because-it-does-not-exist-or-you-do-not-have-permission\"><strong>\u00ab Msg 3701, Level 11, State 5, Line 1<\/strong><br><strong>Cannot drop the table \u2018SALES\u2019, because it does not exist or you do not have permission. \u00bb<\/strong><\/h4>\n\n\n\n<p>You want to DROP a table from your <strong>SQL Server database<\/strong> but using directly the SQL command DROP TABLE followed by the table name, the <strong>RDBMS <\/strong>is throwing an error because the table does not exist.&nbsp;The solution is to test the existence of the table using a simple SQL query.<\/p>\n\n\n\n<p><span style=\"text-decoration: underline;\"><strong>Solution:<\/strong><\/span> Test the SQL Server table existence before the drop table command it with the <strong>IF EXISTS and DROP functions<\/strong>.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">IF EXISTS (\n  SELECT 1 FROM sys.objects\n  WHERE object_id = object_id(N'[dbo].[SALES]')\n    AND type in (N'U') \n)\nBEGIN\n  DROP TABLE [dbo].[SALES]\nEND;\n<\/pre>\n\n\n\n<p>This post shows <strong>how to check if table exists in SQL Server and then drop it<\/strong>, but only if it exists. Check first in the objects system table of the SQL Server database, after this drop the table without any error. Indeed, if the table does not exists then the DROP TABLE command is simply skipped.<\/p>\n\n\n\n<p>Here is a <strong><a href=\"https:\/\/expert-only.com\/en\/t-sql\/display-the-modification-date-of-a-sql-server-table\/\">T-SQL script to check the last modification date of a given table<\/a><\/strong>, from the table management series of articles from the blog.<\/p>\n\n\n\n<p>To go further and learn more details on the syntaxes and options, check this article on the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/drop-table-transact-sql?view=sql-server-ver15\" target=\"_blank\" rel=\"noreferrer noopener\">drop<\/a> table command from the official SQL Server documentation.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-blog-sql-server-et-microsoft-it wp-block-embed-blog-sql-server-et-microsoft-it\"><div class=\"wp-block-embed__wrapper\">\nhttps:\/\/expert-only.com\/en\/t-sql\/display-the-modification-date-of-a-sql-server-table\/\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>How to Check if table exists in SQL Server before to delete it ? How to drop it only if the existence check shows it already exists in the database to avoid errors, using the DROP TABLE keyword. Indeed, <a class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/\" title=\"Check if table exists in SQL Server\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":5855,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[454],"tags":[],"class_list":{"0":"post-7252","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-t-sql"},"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.7 (Yoast SEO v26.2) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Check if table exists in SQL Server - T-SQL<\/title>\n<meta name=\"description\" content=\"Check if table exists in SQL Server before dropping it ? Use this query to check if the table exist in the database and avoid errors.\" \/>\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\/t-sql\/check-if-table-exists-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Check if table exists in SQL Server\" \/>\n<meta property=\"og:description\" content=\"Check if table exists in SQL Server before dropping it ? Use this query to check if the table exist in the database and avoid errors.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/\" \/>\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-04-13T04:11:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-05-12T14:07:42+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/planning-4897792_1920-1024x576.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1024\" \/>\n\t<meta property=\"og:image:height\" content=\"576\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"Check if table exists in SQL Server\",\"datePublished\":\"2022-04-13T04:11:00+00:00\",\"dateModified\":\"2022-05-12T14:07:42+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/\"},\"wordCount\":314,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/planning-4897792_1920.png\",\"articleSection\":[\"T-SQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/\",\"url\":\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/\",\"name\":\"Check if table exists in SQL Server - T-SQL\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/planning-4897792_1920.png\",\"datePublished\":\"2022-04-13T04:11:00+00:00\",\"dateModified\":\"2022-05-12T14:07:42+00:00\",\"description\":\"Check if table exists in SQL Server before dropping it ? Use this query to check if the table exist in the database and avoid errors.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/planning-4897792_1920.png\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/planning-4897792_1920.png\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Check if table exists in SQL Server\"}]},{\"@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 table exists in SQL Server - T-SQL","description":"Check if table exists in SQL Server before dropping it ? Use this query to check if the table exist in the database and avoid errors.","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\/t-sql\/check-if-table-exists-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Check if table exists in SQL Server","og_description":"Check if table exists in SQL Server before dropping it ? Use this query to check if the table exist in the database and avoid errors.","og_url":"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2022-04-13T04:11:00+00:00","article_modified_time":"2022-05-12T14:07:42+00:00","og_image":[{"width":1024,"height":576,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/planning-4897792_1920-1024x576.png","type":"image\/png"}],"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":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"Check if table exists in SQL Server","datePublished":"2022-04-13T04:11:00+00:00","dateModified":"2022-05-12T14:07:42+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/"},"wordCount":314,"commentCount":0,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/planning-4897792_1920.png","articleSection":["T-SQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/","url":"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/","name":"Check if table exists in SQL Server - T-SQL","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/planning-4897792_1920.png","datePublished":"2022-04-13T04:11:00+00:00","dateModified":"2022-05-12T14:07:42+00:00","description":"Check if table exists in SQL Server before dropping it ? Use this query to check if the table exist in the database and avoid errors.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/planning-4897792_1920.png","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2018\/06\/planning-4897792_1920.png","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/t-sql\/check-if-table-exists-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"Check if table exists in SQL Server"}]},{"@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\/7252","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=7252"}],"version-history":[{"count":0,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/7252\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/5855"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=7252"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=7252"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=7252"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}