Dropping Tables
In SQL Server, the DROP TABLE statement is used to permanently remove an existing table from a database. In this section, we will learn how to safely drop tables and understand what happens when a table is deleted.
What is DROP TABLE?
The DROP TABLE statement completely removes a table from the database.
When a table is dropped, its structure and stored records are removed.
DROP TABLE Students;
DROP TABLE Flow
Students Table
↓
DROP TABLE
↓
Table Removed
↓
Data + Table Structure Deleted
Drop a Table
Suppose we have a table named Students. The following statement removes it:
DROP TABLE Students;
After executing this statement, the Students table will no longer exist in the database.
Table Before DROP
| StudentId | StudentName | Age | Course |
|---|---|---|---|
| 1 | Rahul | 22 | Java |
| 2 | Priya | 21 | .NET |
| 3 | Amit | 23 | Python |
Execute DROP TABLE
DROP TABLE Students;
Verify Whether the Table Exists
Before performing operations, we can check whether a table exists in the current database.
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Students';
If the table has been dropped successfully, the query will not return the Students table.
DROP TABLE IF EXISTS
SQL Server also supports DROP TABLE IF EXISTS. It is useful when we want to drop a table only if it exists.
DROP TABLE IF EXISTS Students;
This avoids an error when the specified table does not exist.
Dropping Multiple Tables
Multiple tables can be dropped by using separate DROP TABLE statements.
DROP TABLE IF EXISTS Students;
DROP TABLE IF EXISTS Courses;
DROP TABLE IF EXISTS Departments;
DROP vs DELETE
DROP and DELETE are different SQL operations.
| DROP TABLE | DELETE |
|---|---|
| Removes the complete table. | Removes rows from a table. |
| Table structure is removed. | Table structure remains. |
| Table no longer exists. | Table still exists. |
| Used to remove a database table. | Used to remove records. |
DROP vs TRUNCATE
| Operation | Table Structure | Data |
|---|---|---|
| DROP | Removed | Removed |
| TRUNCATE | Remains | Removed |
⚠ Important Warning
DROP TABLE permanently removes the table structure and its data. Always verify the table name before executing a DROP TABLE statement, especially when working with important or production databases.
Practical Example
The following example creates a temporary table and then removes it.
CREATE TABLE DemoStudents
(
StudentId INT,
StudentName VARCHAR(100)
);
DROP TABLE DemoStudents;
Result
After executing DROP TABLE, the DemoStudents table is removed from the database along with its table structure and stored records.
Summary
In this section, we learned how to remove a table using DROP TABLE, how to check whether a table exists, and the difference between DROP, DELETE and TRUNCATE operations.