Infrastructure at your Service

Stéphane Haby

SQL Server 2016: IF EXISTS is included in the DROP command

With SQL Server 2016, I’m nicely surprised by the addition of IF EXISTS directly in the T-SQL command DROP.
Before this new option, all queries are written with IF EXISTS (SELECT * FROM sys….) DROP object.
I have quickly tested just for 2 drop commands(See List of available objects below):

  • one for a table
  • one for a column in a table

DROP a table

Before SQL Server 2016

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DatabaseLog' AND TABLE_SCHEMA = 'dbo')
DROP TABLE [dbo].[DatabaseLog]

With SQL Server 2016

DROP TABLE IF EXISTS [dbo].[DatabaseLog]

Mdsn reference for DROP TABLE

Drop01

DROP a column

Before SQL Server 2016

IF EXISTS (SELECT * FROM sys.columns WHERE NAME = 'newcolumn' AND Object_ID = Object_ID(N'DatabaseLog') )
ALTER TABLE [dbo].[DatabaseLog] DROP COLUMN newcolumn;

With SQL Server 2016

ALTER TABLE [dbo].[DatabaseLog] DROP COLUMN  IF EXISTS newcolumn

Mdsn reference for ALTER TABLE DROP COLUMN

Drop02

List of available objects

This option is also available for a lot of objects:

  • AGGREGATE
  • ASSEMBLY
  • COLUMN
  • CONSTRAINT
  • DATABASE
  • DEFAULT
  • FUNCTION
  • INDEX
  • PROCEDURE
  • ROLE
  • RULE
  • SCHEMA
  • SECURITY POLICY
  • SEQUENCE
  • SYNONYM
  • TABLE
  • TRIGGER
  • TYPE
  • USER
  • VIEW

As you can see, this option is also available at server level not just at database level like for DROP DATABASE

DROP a database

DROP DATABASE IF EXISTS AdventureWorksDW2012

Mdsn reference for DROP DATABASE

This is a very nice option to know to simplify your future queries… 😎

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Haby
Stéphane Haby

Delivery Manager and Senior Consultant