SQL Server DROP TABLE

In SQL Server, the DROP TABLE statement is used to remove or delete permanently from the database. In SQL Server after performing the DROP Operation we cannot revoke the table or database again, because it is an irreversible action. The Drop Operation is different from the DELETE Command, In the DELETE command we can revoke the database or table after performing the DELETE Operation. Generally, we use the DROP TABLE command to delete or remove those tables that are useless.

Syntax:

DROP TABLE [IF EXISTS] table_name;

Explanation:

• DROP TABLE: The keyword used to delete a table from the database.
• IF EXISTS: This optional clause prevents an error from occurring if the table does not exist and If the table exists then it is dropped otherwise, nothing will happen.
• table_name: It is a table name that we want to drop from our database.

If we want to delete multiple tables at the same time then the below syntax would be follow.

DROP TABLE IF EXISTS table_name1, table_name2, table_name3;

DROP a Table That Doesn't Exist

The following statement deletes the table named Geeksforgeeks.

DROP TABLE IF EXISTS Geeksforgeeks;

Explanation: In this example, this statement will delete the table named Geeksforgeeks, but if the table doesn't exist, then it will not raise any error. because we used the IF EXISTS clause that ensures that the statement doesn't fail in case the table doesn't exist in the database and the statement will be executed successfully.

The Result Looks Like:

DROP a Single Table

Let's create a new table named Student.

CREATE TABLE Student (
    StdtId INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Dob DATE
);

Now, to delete the table Student, we will write the following statement.

DROP TABLE Student;

Explanation: This statement will delete the Student table from the database. It will delete all the records stored in the Student table.

DROP a Table With FOREIGN KEY Constraint

Now, we will create two tables named Student and Course in the University schema. where the student table has a FOREIGN KEY constraint referencing the CourseId column in the Course table.

CREATE SCHEMA University;
GO

CREATE TABLE University. Course
(
    CourseId INT PRIMARY KEY,
    CourseName VARCHAR(100)   
);

CREATE TABLE University. Student
(
   StudentId INT PRIMARY KEY IDENTITY (1,1),
   FirstName VARCHAR(50) NOT NULL,
   LastName VARCHAR(50) NOT NULL,
   Age INT NOT NULL, 
   Address NVARCHAR(255) NOT NULL,
   CourseId INT NOT NULL,
   FOREIGN KEY (CourseId) REFERENCES University.Course (CourseId)
);

Now Let’s try to drop the Course table from the University schema.

DROP TABLE University.Course;

The Result Looks Like:

Explanation: In SQL Serve­r, it is not allowed to delete a table that is referenced by fore­ign key constraints. If we want to remove this type­ of table, we have two options. either we have to delete foreign key constraints, or we have to delete referencing table first. In our example, we have to e­ither remove the fore­ign key constraints from the table or we­ have to remove the University.Stude­nt table first before removing the University.Course table.

DROP TABLE University.Student;
DROP TABLE University.Course;

Explanation: In the above statement, we drop the referencing Student table first and then the Course table. So, it will be executed successfully.

The Result Looks Like:

Conclusion

In conclusion, the DROP TABLE statement in SQL Se­rver is a powerful command. It lets us fully erase­ a table with all its data from the database. Re­member we should always use this statement with caution, as we can't get a droppe­d table back. Before­ executing the DROP TABLE statement, we must confirm that we have­ the neede­d permissions. Also, we should check the existence of the table to prevent errors.