DROP table Command Example in Oracle, MySQL and SQL Server

DROP is one of the basic SQL commands, which is used to DROP database objects. Since it's part of ANSI SQL, the DROP command is supported by all major database vendors, including Oracle, MySQL, and Microsoft SQL Server. A SQL DROP TABLE statement is used to delete a table definition and all data from a table, but DROP can also be used drop index, view, trigger, or any other database object. By the way, be careful with using the DROP table command because once a table is deleted, all the information available in the table is lost forever.

DROP is sometimes also used to clear tables; for example, removing a large table with millions of records by using SQL DELETE command may take a long time; alternatively, you can DROP and recreate the table. In-fact, the difference between TRUNCATE, DELETE, and DROP is one of the popular SQL Interview questions.

Though DELETE allows you to remove selective records, DROP doesn't provide that option. Also, DELETE is a DML command and can be rolled back, but DROP cannot be rolled back. DROP will also remove all indexes, rows, and privileges, and no DML triggers will be fired.

Though DROP behavior is more or less consistent across MySQL, Oracle, and SQL Server, there are some differences like from Oracle 10g table can be "undropped". Execution of the DROP command is also controlled by access control, so you just cannot drop a table if you don't have sufficient privileges.

How to DROP tables in Oracle, MySQL and SQL Server

Let's see the syntax to drop the table from the database.

DROP TABLE "table_name";

This syntax is the purest form, which DROP table with "table_name" in the current database or schema.

1. SQL DROP TABLE Example in MySQL

Let's see the command to drop a table from the MySQL database.

DROP TABLE Session;

2. SQL DROP TABLE Example in Oracle

Let's see the command to drop a table from the Oracle database.

DROP TABLE marketing_emp;

3. How to drop table in Microsoft SQLServer

Here is the SQL DROP table command for Microsoft SQLServer. It's actually similar to MySQL and Oracle. The following command will drop table Employee in your current database or selected schema.

If you are running this from an SQL Script, you can either use USE database command or can prefix, schema name to the table name. This will give enough hint to the database about dropping a table from which schema.

DROP TABLE Employee;   // drop table from currently selected schema

USE company;
DROP TABLE Employee;   // drop table from company schema

DROP TABLE company.Employee;  // another way to drop table from the arbitrary schema

Things to remember about DROP command in SQL

1) DROP is a DDL command.

2) DROP Command cannot be rolled back, except Oracle 10g which provides additional functionality to "undrop" a table as shown in the following example :

SQL> FLASHBACK TABLE Product TO BEFORE DROP;

Flashback complete.

3) DROP table command removes all rows, indexes, and privileges for that table.

4) No DML triggers will be fired as a result of the DROP table command.

5) DROP can be used to clear the database by dropping and recreating them.

6) Difference between DROP and the TRUNCATE command is, DROP command will delete the entire row also the structure. But TRUNCATE command will only remove the contents only, not the structure, so no need to give specifications for another table creation.

That's all about how to drop a table in MySQL, SQL Server, and Oracle. We have not only see DROP table command examples in these three primary databases but also learned a bit about the DROP command in SQL. Since two database/schema may contain tables with the same name, it's wise to prefix schema name before table name while using the DROP command in SQL Server or Oracle. If you do use the DROP command for database clean-up, remember it cannot be rolled back, and no DML trigger is fired.