Drop Tables

The DROP TABLE statement is used to delete one or more tables from a database, along with all associated data, triggers, constraints, and permissions.

---

Syntax:

DROP TABLE [IF EXISTS] [database_name.][schema_name.]table_name;

Key Points:

1. IF EXISTS: Ensures the table is dropped only if it exists (introduced in SQL Server 2016).
2. Database and Schema: Specify the database and schema (optional; defaults to the current database if omitted).
3. Dependencies:
   • Views and stored procedures referencing the table are not automatically dropped.
   • Foreign key constraints must be removed before dropping a referenced table.

---

Examples:

1. Drop a Non-Existing Table:

   DROP TABLE IF EXISTS sales.revenues;

   • No error if the table doesn’t exist.

2. Drop a Single Table:

   DROP TABLE sales.delivery;

3. Drop Multiple Tables:

   DROP TABLE procurement.suppliers, procurement.supplier_groups;

   • The referencing table (suppliers) must be dropped first.

4. Handling Foreign Key Constraints:

   • Drop the referencing constraint or table first:

     DROP TABLE procurement.suppliers;
     DROP TABLE procurement.supplier_groups;

---

Notes:

• Deleting a table removes all its data and constraints permanently.
• Use IF EXISTS to avoid errors when the table is missing.
• Ensure dependencies (e.g., foreign keys) are addressed before dropping a table.