IF Exists MSSQL always errors

Question

I'm trying to drop a table if it exists.

I created a table, that worked fine. I can drop the table with the DROP TABLE command. What I can't do is any form of this:

DROP TABLE IF EXISTS customer;

In fact, I can't seem to get any form of IF EXISTS to work at all. I went to the MS website and looked up how to do this, and their example won't even run:

CREATE TABLE T1 (Col1 int);  
GO  
DROP TABLE IF EXISTS T1;  
GO  
DROP TABLE IF EXISTS T1;

Any ideas?

That way of writing is not valid T-SQL pre-SQL Server 2016. See [here](https://stackoverflow.com/questions/7887011/how-to-drop-a-table-if-it-exists-in-sql-server) for a solution. — TT., Jul 01 '17 at 14:19
Thanks a ton! I was searching using the MSSQL addition, but still not getting anything. However, your link was perfect. Exactly what I was looking for! — Cynon, Jul 01 '17 at 14:26
This was the answer I needed: https://stackoverflow.com/questions/7887011/how-to-drop-a-table-if-it-exists-in-sql-server Thanks for the help guys! — Cynon, Jul 01 '17 at 14:27
Use `SELECT @@VERSION` to check if you are connected to SQL2016+ instance. This syntax (`DROP IF EXISTS`) is available only starting with SQL2016. — Bogdan Sahlean, Jul 01 '17 at 19:15

score 5 · Answer 1 · answered Jul 01 '17 at 14:22

According to the documentation, the IF EXISTS clause is only allowed in Azure SQL Database and SQL Server 2016 or later. So it seems you are using SQL Server 2014 or earlier.

You'll need to first check that the table exists in earlier versions. One method is checking for a OBJECT_ID that is NOT NULL:

IF OBJECT_ID(N'dbo.customer', 'U') IS NOT NULL DROP TABLE dbo.customer;

IF Exists MSSQL always errors

1 Answers1