How to drop a SQL Server table with a script? T-SQL tutorials

How to drop one or multiple SQL Server tables using a T-SQL script, and how to check the existence of the table?

To drop a table with a SQL Server script, simply use the DROP TABLE command. Indeed, the syntax of the MS SQL command to delete a table from a database is straight forward. Just remember to fill in the schema followed by the name of the SQL Server table in the case where a schema other that dbo, for database owner, is used.

Table of Contents

What is the T-SQL DROP TABLE statement?

The DROP TABLE statement is a T-SQL statement used to delete a table from a database. It is a Data Definition Language (DDL) statement that permanently removes the table and all associated elements, including:

data
indexes
triggers
and constraints

The syntax of the DROP TABLE statement is as follows:

DROP TABLE table_name;

Where table_name is the name of the table that you want to delete.

Use Case 1: Drop a single SQL Server table

The most common use case for the DROP TABLE statement is to delete a single table. To delete a table using the DROP TABLE statement, simply specify the name of the table that you want to delete. For example, suppose we have a table named “employees” that we want to delete. We can use the following T-SQL code to accomplish this:

Here is a SQL script to create a SQL Server table, use it to have a sample table in the database. For example, let’s consider a table named dbo.SALES, and use this query to drop the table :

DROP TABLE [dbo].[SALES];

This will delete the dbo.SALES table and all associated data, indexes, triggers, and constraints.

As no existence check is performed, if the SQL server displays this error, then the table doesn’t exist in the current database.

SQL Server Error:
« Msg 3701, Level 11, State 5, Line 1
Cannot drop the table ‘SALES’, because it does not exist or you do not have permission. »

If you DROP a table from a SQL Server database and use directly the SQL command DROP TABLE followed by the table name. If the table does not exist, then the Microsoft Database Management System (RDBMS) is throwing an error.

Use Case 2: Drop multiple SQL Server tables

In some cases, you may need to delete multiple tables at once. The DROP TABLE statement allows you to specify multiple tables in a single statement by separating them with a comma. For example, suppose we have two tables named “employees” and “departments” that we want to delete. We can use the following T-SQL code to accomplish this:

DROP TABLE employees, departments;

This script will delete both the employees and departments tables and all associated data, indexes, triggers, and constraints, in one unique query. On the same, it is a good practice to check the existence of views before deleting them.

Test and drop a SQL Server view if it exists in the database

Use Case 3: Drop a table if it exists

Sometimes, you may want to delete a table only if it exists in the database. To do this, you can use the IF EXISTS clause with the DROP TABLE statement. For example, suppose we want to delete the “employees” table only if it exists. We can use the following T-SQL code to accomplish this:

DROP TABLE IF EXISTS employees;

This will delete the “employees” table if it exists in the database. If the table does not exist, the statement will do nothing.

Use Case 4: Drop a table and create a new one

In some cases, you may want to delete a table and create a new one with the same name and schema. To do this, you can use the DROP TABLE statement followed by the CREATE TABLE statement. For example, suppose we want to delete the “employees” table and create a new one with the same schema. We can use the following T-SQL script to accomplish these two tasks:

DROP TABLE IF EXISTS dbo.employees;

CREATE TABLE dbo.employees (
	id INT PRIMARY KEY,
	name VARCHAR(50),
	age INT,
	salary DECIMAL(10,2)
);

This will delete the dbo.employees table if it exists and create a new one with the same name and schema.

Conclusion on dropping tables using SQL Server

In conclusion, this tutorial on how to drop a SQL Server table with a script shows that it is a powerful statement, frequently used to delete multiple tables from a database. It allows developers to delete single or multiple tables, delete a table if it exists, and delete a table and create a new one with the same schema.

Note that it can easily integrated in a loop to drop all the table from a database. For example if combined with a query to list the tables.

As seen in the tutoriel , a good practice is to test the existence of the table using a simple SQL query. Use the SQL script example to check the existence before drop a table using the EXISTS function. Here is another tutorial on how to manage SQL Server tables, is on how to modify a SQL Server table using the ALTER TABLE command.