SQL Server 2016 - Drop Objects IF EXISTS

In SQL Server 2016 many T-SQL enhancements were introduced. In this article, I am going to share one among them.

In SQL Server 2016 there is a new way to drop objects with simplified syntax.

In previous versions syntax will be as shown below: (this will also work in SQL Server 2016)

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

Below are some examples:



--Drop Constraint

ALTER TABLE [dbo].[Test] DROP CONSTRAINT IF EXISTS ChckConstrnt
ALTER TABLE [dbo].[Test] DROP COLUMN IF EXISTS Name --Drop Column
DROP INDEX IF EXISTS CIX ON [dbo].[Test] --Drop Index 
DROP USER IF EXISTS Dev --Drop User
DROP TABLE IF EXISTS [dbo].[Test] --Drop Table
DROP VIEW IF EXISTS Vw_Test --Drop View
DROP DATABASE IF EXISTS SathyaDB --Drop Database

This syntax also applies to dropping Function & Stored Procedure:

DROP FUNCTION IF EXISTS fn_test --Drop Function
DROP PROCEDURE IF EXISTS Usp_Get_test --Drop Stored Procedure
 
USE tempdb
GO
CREATE TABLE #temptbl(Id INT)
DROP TABLE IF EXISTS #temptbl --Drop temp table

Scripts are tested in Microsoft Azure VM - SQL Server 2016 CTP 3.2 

See Also:

• My T-SQL Posts
• Drop
• Alter