DROP IF EXISTS T-SQL Enhancement in SQL Server 2016

SQL Server 2016 introduces a new DROP IF EXISTS statement to DROP objects such as tables, columns, indexes, stored procedures, schemas, triggers and user-defined functions. DROP IF EXISTS statement can be used to check whether the object exists before it is dropped or removed from the database.

This is Part 3 of 10 Part T-SQL Enhancements in SQL Server 2016 for Developers and DBAs. Click here to read it from the beginning….

Prior to SQL Server 2016 you would write the T-SQL script similar to one below to check whether the object existed before it could be dropped from the database.

Use <DATABASENAME>
GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[SchemaName].[TableName]') 
AND [type] IN (N'U')) 

DROP TABLE [SchemaName].[TableName]
GO

Trending SQL Server Articles and Tips

Example DROP IF EXISTS in SQL Server 2016

Starting SQL Server 2016, you could achieve the same functionality by executing the below simplified T-SQL script.

Use <DATABASENAME>
GO

DROP TABLE IF EXISTS [SchemaName].[TableName]
GO

DROP PROCEDURE IF EXISTS [SchemaName].[ProcedureName]
GO

Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.

Related Topics

• ALTER TABLE WITH (ONLINE = ON | OFF)
• MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE
• ALTER DATABASE SET AUTOGROW_ALL_FILES
• COMPRESS and DECOMPRESS Functions
• STRING_SPLIT and STRING_ESCAPE Functions
• FORMATMESSAGE Statement
• SERVERPROPERTY Function
• TRUNCATE TABLE WITH PARTITIONS
• DROP IF EXISTS