Copyright

SQL DROP Table: Tutorial & Explanation

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

How can you keep your database from being overrun with information? Make some space with DROP TABLE! This lesson will discuss the SQL process for dropping a table (DROP TABLE). The basic syntax will be discussed, as well as examples from two major database systems: Oracle and SQL Server.

SQL DROP TABLE

There are times in database administration where a table is no longer needed and needs to be removed. Whether it is to save space, reduce complexity in the database design, or because the table was temporary in nature, the DROP command can be useful to clear space in the database.

The DROP TABLE command will first delete data from the table, and then remove it from the database space. All data is wiped out from the table!

Because of the power of this command, most database systems secure this function. In Oracle, for example, the Database Administrator (DBA) must have system privileges to run DROP TABLE.

Syntax

The following basic syntax is used when dropping a table:

DROP TABLE table_name;

For example, if there is a table called SALES, the command is: DROP TABLE Sales. This will completely delete all data in the table and then remove the table from the database schema.

Database differences

Different database systems behave differently with the DROP TABLE command. Below are examples from Oracle, MS-SQL and SQL Server.

Oracle

DROP TABLE can either completely delete the table or move it to the recycle bin. There is an additional parameter on the command that will completely delete the table and wipe it from the schema: that is PURGE. Example:

DROP TABLE Temporary_Data PURGE

This also improves security, especially if there is any sensitive data in the table being dropped. For example, if the temporary table stores employee IDs or birth dates, using DROP TABLE with the PURGE parameter prevents any data from going to the recycle bin first (and free from any potential prying eyes).

SQL Server

SQL Server uses the standard DROP TABLE syntax. DROP TABLE #customers will delete the data from, and drop the table #customers.

MS SQL (Transact-SQL)

MS-SQL uses the same syntax; however it allows for an IF EXISTS clause, which tests first to see if the table is in the database. This prevents unusual database behavior. For example, the following syntax checks to see if the browser history table exists before deleting:

DROP TABLE IF EXISTS browser-history;

DROP TABLE cannot be used if the table is connected to other tables with a foreign key.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support