SQL Server 2016 shipped with a lot of TSQL Enhancements and one of them is DROP IF EXISTS. It actually minimizes the size of the script which you can use to drop any object (AGGREGATE, ASSEMBLY, COLUMN, CONSTRAINT, DATABASE, DEFAULT, FUNCTION, INDEX, PROCEDURE, ROLE, RULE, SCHEMA, SECURITY POLICY, SEQUENCE, SYNONYM, TABLE, TRIGGER, TYPE, USER, and VIEW). In other words, you DO NOT need to write long IF EXISTS Statements anymore to drop any object.
The beauty of this TSQL is that if it fails (in case, if object does not exist), it DOES NOT give you error message.
Lets me create a sample table to demonstrate how it works but as I mentioned earlier you can use this script to drop any object.
USE tempdb GO --Create Sample table CREATE TABLE dbo.tbl_Sample ( [ID] INT, [Name] NVARCHAR(50) ) GO
Given below methods are compatible with SQL Server 2005 and above.
--Method 1 USE tempdb GO IF OBJECT_ID('dbo.tbl_Sample', 'U') IS NOT NULL DROP TABLE dbo.tbl_Sample GO --Method 2 USE tempdb GO IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tbl_Sample') DROP TABLE dbo.tbl_Sample GO
Given below method is compatible with SQL Server 2016 and above.
USE tempdb GO DROP TABLE IF EXISTS tbl_Sample; GO
Reference : MSDN