CODEWITHSUNDEEP
sqlsql-server-2008
kooker
kooker

Reputation: 373

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

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?

Upvotes: 7

Views: 22656

Answers (2)

Jovan MSFT
Jovan MSFT

Reputation: 14590

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)

Upvotes: 0

Spiky
Spiky

Reputation: 438

This should do it!

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

Upvotes: 15

Related Questions