SQL DROP – Tables, Views, Etc. – Deleting an Object

Separate from TRUNCATE and DELETE commands, we can use the DROP command to remove objects such as tables in SQL from a database. As a result, it’s important to be extra careful when using the DROP command in SQL when we drop an object. Often times, these commands are restricted to administrators to prevent accidental object deletions.

It’s important remember that DROP will delete objects such as schemas, tables, views, indexes, and procedures. Truncate and delete commands will only delete data stored in tables. We can see how the syntax looks for each of these.

DROP SCHEMA <schema_name>; — Deleting a schema in a SQL database

DROP TABLE <schema_name>.<table_name>; — Deleting/Removing a table in SQL

DROP VIEW <schema_name>.<view_name>; — Deleting/Removing a view in SQL

DROP INDEX <table_name>.<index_name>; — Deleting an index on a table in SQL

DROP PROCEDURE <schema_name>.<procedure_name>; — Deleting a procedure in SQL

More DROP Commands

We can see that the DROP command covers a good amount of objects. These aren’t the only objects, but arguably the most used. We’re also able to apply the DROP command to users and triggers.

DROP TRIGGER <trigger_name>; — Deleting a trigger

DROP USER <user_name>; — Removing a user from the database

We must re-iterate the importance of the drop command. It’s a powerful function for us to be able to use SQL to drop an object from a database. However, if done by accident it can be a painful and embarrassing lesson. Most of the time we can recover objects if they were accidentally removed, but not always. As a result, it’s important to have proper database backup procedures in place. Use with caution.

As shown in the example above, the database will highlight the key terms that are available. Some databases only use PROC instead of PROCEDURE as the keyword so be sure to refer to your database documentation for correct syntax.