Drop Table if Exists

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.

Use caution when dropping tables. Once you drop a table, you will not be able to get it back unless you restore a backup.

[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.

3 comments
AJ 06 Sep 2016 at 9:06 pm

DROP TABLE IF EXISTS [dbo].[Member]
GO

returns an error message
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword ‘IF’.

SQL Server 2016
Microsoft SQL Server Management Studio – 13.0.15700.28

Jovan Popovic 03 Nov 2015 at 11:27 am

In SQL Server 2016 you can use DROP IF EXISTS:

DROP TABLE IF EXISTS #temp
CREATE TABLE #temp (id int, name nvarchar(20))

See:
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016.aspx

Derek Dieter 28 Mar 2016 at 7:56 pm

Oh how awesome is that!! Thank you Jovan!

Featured Articles

 Site Author

  • Thanks for visiting!
css.php