top of page

How to use DROP command to remove tables in Oracle, MySQL and SQL Server

Updated: Mar 23, 2023

The DROP command is an SQL statement used to remove or delete objects, such as tables, views, indexes, or other database objects, from a database schema. The exact syntax and behaviour of the DROP command can vary between different SQL database management systems, but generally, the command is used to remove an object from the database schema permanently.


The DELETE command allows you to remove selective records but the DROP command deletes the entire table.


Syntax:

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

DROP TABLE table_name;

When you use the DROP command, you need to be careful because it will permanently delete the object and all of its associated data. Therefore, it is important to take a backup before executing the DROP command, especially in a production environment.


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 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, the 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.


Conclusion

The DROP command is a tool in SQL that allows you to remove database objects when they are no longer needed. However, it is important to use the command with caution to avoid losing important data or accidentally removing critical components of your database schema.


0 comments

留言


bottom of page