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

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

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… 😎