Drop Table in SQL Server

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.

Drop Table Syntax

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

Now, You can delete the table using the following drop table query.

Drop Table if Exists

Now, run a query to delete a table department, which does not exists in the database.

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.

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.

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.

You can delete both the tables using a single query

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.

Next, let us create another database Sales. And make it current by using the Use Sales

Finally, wou can use the fully qualified table name to delete the table from the HR database

Drop Table using GUI Tool

We can also drop the table using the Table Designer

  1. Open SSMS and connect to the database instance.
  2. Select the database (HR) from the Databases node
  3. Under Tables select the table, which you wish to delete
  4. Right click and click on Delete option.
Drop Table using SSMS

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.

Reference

Drop Table TSQL Reference

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top