DROP TABLE IF EXISTS not working on Azure SQL Data Warehouse

Question

I used the SQL Server management studio to generate script against Azure Data Warehouse. I selected Edition Azure Data Warehouse, it generates below the script to drop table if it exists and create table. However, the script cannot pass validation. Please see below for the error message.

DROP TABLE IF EXISTS Table1
GO

Error message:

Parse error at line: 2, column: 12: Incorrect syntax near 'IF'.

Answer 1

As indicated in the documentation, DROP TABLE IF EXISTS is not currently supported in Azure SQL Data Warehouse, as at today:

-- Syntax for SQL Server and Azure SQL Database  
DROP TABLE [ IF EXISTS ] [ database_name . [ schema_name ] . | schema_name . ]  
table_name [ ,...n ]  
[ ; ]

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse    
DROP TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name   
[;] 

Answer 2

Bob's answer is correct. I tried to add the following as a comment, but it looked terrible without formatting.

You can use the following syntax as an alternative:

if object_id ('dw.dim_location','U') is not null drop table dw.dim_location;

Answer 3

Example from Microsoft's documentation:

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
    DROP TABLE #stats_ddl
END

Replace stats_ddl with whatever you named your temp table.

Turning that into a one-liner like Ron's example works too:

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL DROP TABLE #stats_ddl;

Answer 4

Just to add another option, the code below should work for SQL Server on premises and Azure SQL DB as well, this is the option when you choose "Script as Drop" from Azure Data Studio:

--
--
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[your_table]') AND type in (N'U'))
DROP TABLE [dbo].[your_table]
--
;