How to Drop a Database Table Only If It Already Exists

If you use a database for long enough, you will probably run into a situation where you want to drop a table if it exists. Often the reason you'll want to do this is because you need to create a table and you do not want to run into an error when you try to create it because a table with that name already exists, but do not want to encounter an error when you drop it because it doesn't exist. Whatever your motives may be, this is how you can drop a table if (and only if) it already exists in MySQL, MS SQL, and Oracle.

Things You'll Need

• Oracle, MySQL, or MS SQL Server
• A table you are itching to get rid of

Instructions

1. MySQL was kind enough to make it very straightforward to drop an existing table. To drop the unfortunately-named table 'Drop_Me', run the following query -

   DROP TABLE IF EXISTS Drop_Me

2. MS SQL makes it slightly harder to drop a table if it exists. In order to accomplish this, you will need to use an IF statement and the EXISTS function -

   IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'Drop_Me') AND type = (N'U'))
   DROP TABLE Drop_Me

3. Oracle takes the complexity of dropping a table if it exists to the next level. In this piece of sample code, you need to know the owner of the table, which happens to be 'Me' -

   DECLARE
   v_count NUMBER :=0;
   BEGIN
   SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='Drop_Me' AND owner='Me';
   IF v_count = 1 THEN
   EXECUTE IMMEDIATE 'DROP TABLE Me.Drop_Me';
   END IF;
   END;

Tips & Warnings

• Make sure you really want to drop the table before you run one of these commands! You won't be asked for confirmation.

• Other databases likely have a completely different syntax for dropping a table if it exists.