SQL Server: DROP TABLE Statement

In this post explains how to use the DROP TABLE statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) DROP TABLE statement allows you to remove or delete a table from the SQL Server database.

Syntax

The syntax for the DROP TABLE statement in SQL Server (Transact_SQL) is:

DROP TABLE table_name;

Parameters or Arguments

table_name

The name of the table to remove from the SQL Server database.

Example

Let's look at an example that shows how to drop a table using the DROP TABLE statement in SQL Server (Transact-SQL).

For example:

DROP TABLE employees;

This DROP TABLE example would drop the table called employees from the current database in SQL Server.

What happens if the table that you wish to delete is not in the current database, but rather in another database in SQL Server? Let's look at how to drop a table in another database.

For example:

DROP TABLE Database2.dbo.suppliers;

This DROP TABLE example would drop the table called suppliers that is not in the current database in SQL Server. Rather, the suppliers table is found in another database called Database2 on the SQL Server instance.

Since we are referencing the database name, this DROP TABLE statement could be executed from any database on the server instance (provided that you have the required DROP privileges).

Next, let's look at how to drop a LOCAL TEMPORARY TABLE using the DROP TABLE statement. First it is important to note that all LOCAL TEMPORARY TABLES are prefixed with the # character.

For example:

DROP TABLE #employees;

This DROP TABLE example would drop the LOCAL TEMPORARY TABLE called #employees.

Finally, let's look at how to drop a GLOBAL TEMPORARY TABLE using the DROP TABLE statement. While LOCAL TEMPORARY TABLES are prefixed with the # character, GLOBAL TEMPORARY TABLES start with ## characters.

For example:

DROP TABLE ##suppliers;

This DROP TABLE example would drop the GLOBAL TEMPORARY TABLE called ##suppliers.