Drop Table if Exists
-
Posted on August 30, 2012 by Derek Dieter
-
4
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.
- Comments (RSS)
- Trackback
- Permalink
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