Azure Synapse DROP TABLE IF EXISTS Alternatives

Many relational databases such as Netezza supports DROP TABLE IF EXISTS syntax to safely drop table if it is already present in the database. The Microsoft SQL Server 2016 or higher version supports drop table if exists. However, same command won’t work on Azure Synapse dedicated SQL pool server. In this article, we will check what are the Azure Synapse analytics DROP TABLE IF EXISTS alternatives with some examples.

Azure Synapse DROP TABLE IF EXISTS Alternatives

The DROP TABLE IF EXISTS statement checks the existence of the table in the schema, and if the table exists, it drops.

For example, following statement will work on Microsoft SQL Server 2016 or higher version without any issue.

DROP TABLE IF EXISTS  #Customer
GO
CREATE TABLE #Customer
(
 CustomerId int,
 CustomerName varchar(50), 
 CustomerAdress varchar(150)
)

However, above statements will end up with an “Incorrect syntax near 'IF'.” error in Azure Synapse dedicated SQL pool server.

Following are two DROP TABLE IF EXISTS Alternatives in Azure Synapse.

Using OBJECT_ID Function to check Table Existence
Using sys.tables System Table to check Table Existence

Now, let check these tow options in with some examples

Using OBJECT_ID Function to check Table Existence

The OBJECT_ID function is a TSQL function and you can use it to obtain the identification number of the database object in the Azure Synapse analytics server. The database object can be anything such as table, view, function, etc. OBJECT_ID function can take the object’s name as a parameter and returns the ID if present.

In short, we can use this function to check the existence of any object in the particular database.

The following query will check the #Customer table existence in the tempdb database, and if it exists, it will be dropped.

IF OBJECT_ID(N'tempdb..#Customer') IS NOT NULL
BEGIN
DROP TABLE #Customer
END
GO
CREATE TABLE #Customer
(
 CustomerId int,
 CustomerName varchar(50), 
 CustomerAdress varchar(150)
);

Using sys.tables System Table to check Table Existence

The second method is to use sys.tables system table to check the existence of the table in Azure synapse analytics server.

The following query will check the Customer table existence in the default dbo database, and if it exists, it will be dropped.

IF EXISTS(SELECT [name] FROM sys.tables WHERE [name] like 'Customer%') 
BEGIN
   DROP TABLE Customer;
END;
 
CREATE TABLE Customer
(
 CustomerId int,
 CustomerName varchar(50), 
 CustomerAdress varchar(150)
)

Note that, this method will work with permanent tables. However, the same will not work with a temporary table. Use first method if your requirement is to drop and create temp table on Azure Synapse dedicate SQL pool server.