in SQL Server we use the drop table statement to delete a table & data stored in it from the database. We can also drop the multiple tables in a single query.
Table of Contents
Drop Table Syntax
1 2 3 4 5 | DROP TABLE [IF EXISTS] { database_name.schema_name.table_name } [ ,...n ] ; |
The syntax starts with DROP TABLE.
The optional IF EXISTS clause instructs the SQL Server to check if the table exists before deleting. The option IF EXISTS is only available from SQL Server 2016 onwards.
Specify the name of the table that you want to delete. You can specify the fully qualified table name in the format database_name.schema_name.table_name. If you only specify the table name, then SQL Server user uses the the current database & default schema (dbo unless changed).
You can specify more than one table just by separating the each table by a comma.
Drop Table Example
Create a new database and create a table Employee
1 2 3 4 5 6 7 8 | CREATE TABLE Employee ( EmployeeID int NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, ) |
Now, You can delete the table using the following drop table query.
1 2 3 | Drop Table Employee |
Drop Table if Exists
Now, run a query to delete a table department, which does not exists in the database.
1 2 3 | Drop Table Department |
The SQL Server will raise the following error.
Cannot drop the table ‘Department’, because it does not exist or you do not have permission.
This is where we use the IF EXISTS clause. Now run the following SQL query. This clause is available since SQL Server 2016 onwards.
1 2 3 | Drop Table IF EXISTS Department |
Now SQL server does not return any errors. The table Department if exists will be deleted by SQL Server.
In older version of SQL Server, you can use the following query to delete a table only if it exists.
1 2 3 4 5 6 7 | IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Department') DROP TABLE Department |
Drop Multiple Tables
You can delete the multiple tables in a single SQL query by separating each table with a comma.
Create following tables in the current database.
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE Table1 ( Table1ID int NOT NULL PRIMARY KEY, col1 varchar(50) NOT NULL, ) CREATE TABLE Table2 ( Table1ID int NOT NULL PRIMARY KEY, col1 varchar(50) NOT NULL, ) |
You can delete both the tables using a single query
1 2 3 | drop table Table1, Table2 |
Drop Table in another database
You can drop a table, which exists in another database and schema by using the fully qualified table name.
In the following example, We create a new database HR and create the table Employee.
1 2 3 4 5 6 7 8 9 10 11 | Create database HR Use HR CREATE TABLE Employee ( EmployeeID int NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, ) |
Next, let us create another database Sales. And make it current by using the Use Sales
1 2 3 4 | Create database Sales Use Sales |
Finally, wou can use the fully qualified table name to delete the table from the HR database
1 2 3 4 5 6 7 | // This will throw an error as there is no employee table in current database Drop Table Employee //Deletes the table from HR database Drop Table HR.dbo.Employee |
Drop Table using GUI Tool
We can also drop the table using the Table Designer
- Open SSMS and connect to the database instance.
- Select the database (
HR) from the Databases node - Under Tables select the table, which you wish to delete
- Right click and click on Delete option.
Important Notes on Drop Table
Dropping of a Table is a permanent operation. The table cannot be recovered. Only way to get it back is restoring it from a backup.
DROP TABLE will not delete the table, if it is referenced by a FOREIGN KEY constraint. You need to delete the FOREIGN KEY constraint or drop the referencing table first before dropping the table.
When SQL Server drops a table, it also deletes all data, triggers, constraints, permissions of the table.
The SQL Server drops the table even if a view or stored procedure references it. Those views or stored procedures will throw an error if we attempt to use them. Hence, you need to update or drop the referencing views and stored procedures.
