SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

MySQL – How to Drop Table If Exists in Database?

on March 28, 2016

In this post SQL Server – 2016 – T-SQL Enhancement “Drop if Exists” clause, we have seen the new feature introduced in SQL Server version 2016. MySQL already has this feature for a while and if you are MySQL DBA, you may find it interesting that SQL Server just introduced this feature.

Let us see this example in action now. First, we will create a sample table.

CREATE TABLE test(id INT,name VARCHAR(100));
If we have to drop above table, we can execute following script and drop it.

MySQL - How to Drop Table If Exists in Database? mysql-drop1

DROP TABLE IF EXISTS test;
MySQL – How to Drop Table If Exists in Database? mysql-drop1

The above script drops the table named test if exists, otherwise it will display a warning and not an error. This is very important to understand that as it is not displaying error, we can easily work around the warning in our code.

This is helpful if you are not sure the existence of the table in advance.

MySQL - How to Drop Table If Exists in Database? mysql-drop2

MySQL – How to Drop Table If Exists in Database? mysql-drop2

Not only tables, but MySQL has similar feature available for stored procedures as well. Let us see a quick example for the same.

Here is a script which you can use to create stored procedures.
CREATE PROCEDURE test_proc
AS
BEGIN
SELECT 1 AS number;
END;
After creating the above procedure you can use the following script to drop it.
DROP PROCEDURE IF EXISTS test_proc;
Remember, just like table, it will drop the stored procedure if there is one exist, otherwise, it will just return a warning. It is really interesting to see MySQL already having this feature for a while, whereas SQL Server just introduced this feature in this year.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: