Thus, it prevents the old way of writing if condition and inside the if condition writing a statement to test the object’s presence to drop it. If it does not occur, the next statement in the batch will continue to be executed. But if we try to drop an object that does not exist then it will raise an error message as shown below. Execute the following query. The output will be like this.
Syntax
Arguments
OBJECT_TYPE: The object type can be anyone from the database, trigger, assembly, sequence, index, table, procedure view, function, etc. IF EXISTS: It is an optional clause and if it is mentioned in the DROP statement, it will check the object’s existence, if it exists it will drop, otherwise it will continue to execute the next statement in the block without producing any errors. Now with the introduction of the new method “DROP IF EXISTS” in SQL Server 2016 developers can write brief code. First, create a database named “appuals”. Now, we will create a table to be dropped by executing the following code. The output will be as follows. Further, create a store procedure to be dropped using the following code. The output will be as below.
Old method: Prior to SQL Server using drop if exits on database objects
Using the DROP IF EXISTS method before SQL Server 2016 required writing lengthy IF statement wrappers code.
Drop table if exists
The prior method of dropping a table is as follows. If we are using SQL Server 2015 or earlier than we need to execute the following bunch of code. The output will be as follows. Now its syntax is quite confusing so if you don’t like it and you are using SQL Server 2016 or above you can go for simple DROP IF EXIST statement instead of big wrappers.
Drop store procedure if exists:
To drop the procedure, we have to write a conditional statement to check if the store procedure exists or not then write the drop statement. Otherwise, it will raise an error in case the stored procedure does not exist. Now execute the following statements to drop the procedure in versions lower than SQL Server 2016. The output will be like this.
Drop database if exists:
If you are using previous versions of SQL Server you need to execute the following code to drop the database. The output will be as follows.
New method: DROP IF EXISTS supported in SQL Server 2016 and above version
To drop a database object in SQL Server 2016 and above we need to execute a simple statement.
Drop table if exists:
We can write a statement as below in SQL Server 2016 to remove a stored table if it exists. The output will be like this.
Drop procedure if exists:
Now we will drop the stored procedure we created at the start of the article by executing the following code. The output will be as below. As far as syntax is concerned, this simple statement is easy to understand and easy to remember. Similarly, we can follow the same procedure to drop other database objects.
Drop database if exists:
Execute the following code if you want to drop database by using if exists check The output will be as below. The benefit of using this method is that if the database doesn’t exist it won’t cause any error, the next statement in the batch will continue to be executed. Let’s try to re-drop the already dropped database. Similarly, we can drop indexes, views, sequences, assemblies, etc. from the database.
Fix: Cannot Create a File When That File Already ExistsFix: Error 0x8007232b ‘DNS Name Does not Exists’How to Fix “the selected task no longer exists” on Windows 10[FIX] ‘The Specified Account Already Exists’ (Error 1316) in InstallShield…
