KapilSQLgeek

SQL Server – DROP IF EXISTS in SQL Server 2016

Advertisements

Hi friends, in SQL Server 2016 to check the existence of the object a new optional clause IF EXISTS gets introduced which can be used with the existing DROP statement.

Previously, within IF clause we were checking whether the object is exist or not.

Here is the syntax that we were using in previous versions of SQL server:

–Previous version syntax

IF OBJECT_ID(‘dbo.Test_IfExists’,’U’) IS NOT NULL

DROP TABLE Test_IfExists

IF EXISTS (SELECT * FROM sys.objects where name = ‘Test_IfExists’)

DROP TABLE Test_IfExists

Now, in SQL Server 2016 we can rewrite the above statements in this way –

—DROP IF EXISTS 2016

DROP TABLE  IF EXISTS Test_IfExists

Cool, see the length of code is reduced now and looks simpler :).

Syntax of DROP IF EXISTS i.e. DIE is –

DROP object_type IF EXISTS object_name

Some object_type that be included in DROP clause are like –

Tables, Database, Function, Trigger, Stored Procedure, Column, User, View, Schema etc……

Similarly we can drop the column from a table in this manner –

Syntax

ALTER object_type object_name

DROP object_type If EXISTS object_name

Here we drop the column of a table –

ALTER TABLE Test_Ifexists

DROP COLUMN If EXISTS Name

Hope you like this post !!

Advertisements