{"id":2412,"date":"2012-08-30T21:57:31","date_gmt":"2012-08-31T04:57:31","guid":{"rendered":"https:\/\/sqlserverplanet.com\/?p=2412"},"modified":"2012-11-06T00:26:17","modified_gmt":"2012-11-06T00:26:17","slug":"drop-table-if-exists","status":"publish","type":"post","link":"https:\/\/sqlserverplanet.com\/tsql\/drop-table-if-exists","title":{"rendered":"Drop Table if Exists"},"content":{"rendered":"

To determine if a table exists, it’s best to go against the sys.objects view by querying the object_id using the fully qualified name of the table. The additional ‘type’ field in the where clause ensures that the table that is about to be dropped is a User table and not a system table.<\/p>\n

\r\n\t\t\t\t\t\t\"\"\r\n\t\t\t\t\t\t
\r\n\t\t\t\t\t\t\tUse caution when dropping tables. Once you drop a table, you will not be able to get it back unless you restore a backup.\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<\/div>\n

[cc lang=”sql”]
\nIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DumpMe]’) AND type in (N’U’))
\nDROP TABLE [dbo].[DumpMe]
\n[\/cc]<\/p>\n

To drop a temp table you need to look in the tempdb database for it’s existence.<\/p>\n

[cc lang=”sql”]
\nIF OBJECT_ID(‘tempdb..#Temp’) IS NOT NULL
\nBEGIN
\nDROP TABLE #Temp
\nEND
\n[\/cc]<\/p>\n

Once again, I would highly recommend double checking your code prior to issuing these statements.<\/p>\n","protected":false},"excerpt":{"rendered":"

To determine if a table exists, it’s best to go against the sys.objects view by querying the object_id using the fully qualified name of the table. The additional ‘type’ field in the where clause ensures that the table that is about to be dropped is a User table and not a system table. [cc lang=”sql”] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DumpMe]’) AND type in (N’U’)) DROP TABLE [dbo].[DumpMe] [\/cc] To drop a temp table you need to look in the tempdb database for it’s existence. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..#Temp’) IS NOT NULL BEGIN DROP TABLE #Temp END [\/cc] Once again, I would highly recommend double checking your code prior to issuing these statements.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_s2mail":"yes","footnotes":""},"categories":[34],"tags":[],"class_list":["post-2412","post","type-post","status-publish","format-standard","hentry","category-tsql"],"_links":{"self":[{"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/posts\/2412","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/comments?post=2412"}],"version-history":[{"count":8,"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/posts\/2412\/revisions"}],"predecessor-version":[{"id":2675,"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/posts\/2412\/revisions\/2675"}],"wp:attachment":[{"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/media?parent=2412"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/categories?post=2412"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverplanet.com\/wp-json\/wp\/v2\/tags?post=2412"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}