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.