How to remove tables in Oracle, MySQL and SQL Server? DROP table Example

 Hello guys, if you want to learn about DROP command in SQL then you have come to the right place. Earlier, I have shared the best free SQL and Database courses and several tutorials to learn SELECT, GROUP BY, and other important commands, and in this article, I will show you to use the DROP command in SQL. 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 to 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 the 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 SQL Server

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

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

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

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

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

.

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 the DROP and the TRUNCATE command is, DROP command will delete the entire row also the structure. But the 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 databases/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.

Thanks for reading this article so far. If you like this SQL DROP command tutorial then please share it with your friends and colleagues. If you have any questions or feedback then please drop a note.