SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


How to use SQL DROP Table If Table Exists in SQL Database


On SQL Server data platform, if developers want to drop database table which might have already created in the SQL database, they can use SQL DROP TABLE command. The SQL Drop Table command will run successfully if SQL database table exists on that SQL Server database. Contrarily if the sql table does not exist in SQL Server database, then sql DROP TABLE command will cause sql engine to throw the following error.

Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'Sample SQL Table', because it does not exist or you do not have permission.

In order to modify sql script to drop existing sql tables, SQL developers can use conditional DROP statements. It is as simple as adding an tsql IF clause before executing the sql DROP TABLE command.


SQL Server 2016 Drop Table Command Syntax

With the release of SQL Server 2016, SQL developers and database administrators are able to use a new Drop Table command with new options introduced to check the existence of SQL table being dropped.

One of new SQL Server 2016 enhancements for SQL programming is following Drop Table syntax:

Drop Table[ If Exists ] [ database_name . [ schema_name ] . | schema_name . ] table_name
Code

If you try to drop a database table which has not been created yet on SQL Server 2016 without If Exists clause, you will get following error:

drop table myTable;
Code

Msg 3701, Level 11, State 5, Line 6
Cannot drop the table 'myTable', because it does not exist or you do not have permission.

But when T-SQL developer uses the Drop Table If Exists syntax, it will work successfully without an exception

drop table if exists kodyazTable;
Code

SQL Server 2016 Drop Table If Exists syntax


Drop Table Command for SQL Server 2014 and prior versions

Unfortunately, if you are working on a SQL Server data platform which is SQL Server 2014 or previous versions, you have to check manually the existence of the target database table before you explicitly execute the Drop Table command.

Actually we use the If Exists in the different order as we used in the new SQL Server 2016 syntax and in a seperate statement.

Here is a sample sql code to drop existing table

Create Table [Sample SQL Table] (id_column int);

if exists (
  select * from sys.tables where name = N'Sample SQL Table'
)
  drop table [Sample SQL Table]
Code

Here is a modified version of the above SQL script which can be sefely used on SQL Server 2014 and prior versions.

Drop Table on a SQL Server database

An here is an other sql code that can be used to check if sql table exists already in the SQL Server database, and if exists dropping sql table.
If sql table is not existing on the SQL Server database then the T-SQL DROP Table command will not be executed.

Create Table [Sample SQL Table] (id_column int);

if OBJECT_ID(N'Sample SQL Table',N'U') IS NOT NULL
  drop table [Sample SQL Table]
Code

SQL Server Object_ID function before execute Drop Table

SQL Server developers can use one version of the above three sample sql codes to drop table in SQL databases.

If database developers want to test the existence of a temporary table on SQL Server database, they can use the method described in detail at SQL Server tutorial Check If Temporary Table Exists in SQL Database.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.