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
- Fix Cannot open user default database. Login failed. Login failed for user SQL Server Error
- Different Ways to Find Default Trace Location in SQL Server
- How to Fix “BACKUP detected corruption in the database log” Error in SQL Server
- How to Recycle SQL Server Error Log file without restarting SQL Server Service
- Different States of SQL Server Database
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.
- 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