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