How to drop a table in SQL Server 2008 only if exists

Question

Our company is migrating one of its products from SQL Server 2005 to 2008, and in the logs I've noticed excess of errors about deleting tables if it does not exist. In SQL Server 2005 we had this to delete tables

IF OBJECT_ID('dbo.Units', 'U') IS NOT NULL
   DROP TABLE dbo.Units

which doesn't seem to work anymore.

What would be the proper way of deleting a table only if it exists in SQL Server 2008?

Duplicate of http://stackoverflow.com/questions/5952006/how-to-check-if-table-exist-and-if-it-doesnt-exist-create-table-in-sql-server-20 — SoulTrain, Feb 11 '15 at 23:26

score 13 · Accepted Answer · edited Feb 12 '15 at 00:28

13

This should do it!

IF EXISTS 
        (SELECT 
             TABLE_NAME 
         FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableToDrop') 
DROP TABLE TableToDrop

edited Feb 12 '15 at 00:28

Rigel1121

2,022
1
17
24

answered Feb 11 '15 at 23:24

Spiky

408
1
4
11

score 0 · Answer 2 · answered Nov 03 '15 at 13:15

This code works on my SQL Server 2012:

IF OBJECT_ID('t', 'U') IS NOT NULL DROP TABLE t
CREATE TABLE t(id int)

Maybe your name is wrong (e.g. case sensitive collation on new installation?). What kind of error you are getting? Note that in SQL Server 2016 you can use DROP IF EXISTS:

DROP TABLE IF EXISTS t
CREATE TABLE t(id int)

How to drop a table in SQL Server 2008 only if exists

2 Answers2