Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

An error appear when I use "DROP TABLE IF EXISTS" in SSMS v.17.1

I am working with the version v.17.1 of the SQL Server Management Studio. And theoretically, the query DROP TABLE IF EXISTS works from SQL Server 2016.

You can see this in the official web page of Microsoft: DROP TABLE

However, when I try to execute the query

DROP TABLE IF EXISTS dbo.EmpLocations;

an error appears:

Incorrect syntax near the keyword 'IF'.

What is the problem? I'm using a specific database of Microsoft to work with SQL Server 2016 - 2017.

like image 713
jorge_vicente Avatar asked Aug 04 '17 06:08

jorge_vicente


3 Answers

for backwards compatibility with all versions of sql-server, use this:

IF OBJECT_ID('dbo.emplocations') IS NOT NULL
    DROP TABLE dbo.emplocations;
GO
like image 129
Xingzhou Liu Avatar answered Oct 12 '22 23:10

Xingzhou Liu


We have many ways to do this but generally it's not a practice to DROP and CREATE the physical tables you can use temporary tables instead

Check Existence of real table and DROP:

IF EXISTS(SELECT 1 FROM sys.objects where name = 'myTable' AND type = 'U')
    DROP TABLE dbo.myTable

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

Check existence of temporary table and DROP:

IF OBJECT_ID('tempdb.dbo.#myTable', 'U') IS NOT NULL
    DROP TABLE #myTable;
like image 25
Susang Avatar answered Oct 13 '22 00:10

Susang


Finally, It was my mistake :(

As other people have said, I was using the SQL Server Management v.17.1; however, I was working yet with the version of SQL SERVER 2014. I began to install the new SQL 2016, but I only installed the SSMS17, and not the SQL Server 2016 itself.

Now, I have installed correctly the version of SQL SERVER 2016. And I can execute the query DROP TABLE IF EXISTS Sales.Orders2; without any problem!

If I go to the version (SELECT @@VERSION), I can see that I have the correct installed: Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206.0 (X64) Jul 6 2017 07:55:03 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows 10 Home 6.3 (Build 15063: )

And remember that the new version of SQL2016 they have introduced this new way to drop tables https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/

like image 43
jorge_vicente Avatar answered Oct 13 '22 00:10

jorge_vicente