How to drop table in SQL Server

Overview:

The DROP TABLE SQL statement is used to delete existing table in SQL server database. When we remove a table from the database, it also removes all dependencies to that table like permissions specifications, constraints, definition, data, indexes etc

Warning: When you drop or delete any existing table from your SQL database, it remove all the information related to that table and you will lost it forever so, you must be careful when you use DROP TABLE statement.

While working with the database, sometime it may happen that table is no longer required and here you can drop your table from the database.

Syntax:

DROP TABLE [database_name.schema_name.table_name];
DROP TABLE IF EXISTS [database_name.schema_name.table_name];

First is, you need to specify DROP TABLE statement.

IF EXISTS: This is an optional clause in DROP TABLE statement. If table does not exist in the database and you will try to delete it, it will throw the error. If you specify IF EXISTS clause, system will check table if it exist in the database, then it will remove only or it will display appropriate message if table doesn’t exists in the database.

Database Name (Optional): Specify the database name, then specify the table name which you have created in the database, then the name of the schema to which the table belongs. If no database name specified, it will delete table from the database which is connected with SQL Server.

Table Name: Specify the table name which you want to delete from the database

Note: Views and Stored Procedures are not being deleted with DROP TABLE SQL statement if they are referenced to the dropped table. To delete them, you must have to use DROP VIEWS and DROP PROCUDURES before you drop your table from the database.

How to drop multiple tables?

You should be able to remove multiple tables also by the help of following syntax.

DROP TABLE [database_name.][schema_name.] table_name_1,
[schema_name.] table_name_2,
[schema_name.] table_name_3,
[schema_name.] table_name_4,
...
[schema_name.] table_name_N;

In above syntax to drop the multiple tables, you can use a single DROP TABLE sql statement and it will remove multiple tables from your database.

DROP TABLE Examples:

Before we understand DROP TABLE with an example, let’s create a sample table first. As we already learn how to create a table in sql server database, we will create the following table.

CREATE TABLE dbo.Customers (
[Id] [bigint] IDENTITY(1,1) PRIMARY KEY,
[FirstName] [nvarchar](30) NULL,
[LastName] [nvarchar](30) NULL,
[DOB] [date] NULL,
[Active] [bit] NULL);

Above tables will be created and you will see “Commands completed successfully.” as success message.

1. DROP a single table with SQL statement

DROP TABLE dbo.Customers;

In above SQL statement, “Customer” table will be removed from database with all the data from it.

2. DROP a table using constraints

Let's create another table “CustomerAddress” with having FOREIGN KEY constraint. In this table, “CustomerId” column referenced to "Customers" table with “Id” column.

CREATE TABLE dbo.CustomerAddress ( [Id] [bigint] IDENTITY(1,1) PRIMARY KEY,
[CustomerId] [bigint] NULL,
[AddressName] [nvarchar](100) NULL,
[Address] [nvarchar](100) NULL,
[City] [nvarchar](30) NULL,
[State] [nvarchar](5) NULL,
[Zipcode] [nvarchar](6) NULL,
[Country] [nvarchar](50) NULL,
[Phone] [nvarchar](20) NULL,
[EmailAddress] [nvarchar](100) NULL,
FOREIGN KEY (CustomerId) REFERENCES dbo.Customers (Id));

DROP TABLE dbo.Customers;

This will give the error message as following because it referenced with other table called “CustomerAddress”

Could not drop object 'dbo.Customers' because it is referenced by a FOREIGN KEY constraint.

In SQL Server, It directly doesn’t allow you to remove a table if it is referenced by FOREIGN KEY constraint with another table. To remove this table, either you have to remove FOREIGN KEY constraint or remove that referenced table first. In above example, either you have to remove FOREIGN KEY from “CustomerAddress” table or remove that table first, then remove “Customers” table.

Example 1:

DROP TABLE dbo.CustomerAddress,dbo.Customers;

Example 2:

DROP TABLE dbo.CustomerAddress;
DROP TABLE dbo.Customers;

3. DROP a multiple table with SQL statement

DROP TABLE dbo.CustomerAddress,dbo.Customers;

In above example, both tables “CustomerAddress” and “Customers” will be removed from the database. You can add multiple tables by using comma “,” separated and it will remove all specified tables from the database.

4. DROP a table which doesn’t exists in database

DROP TABLE IF EXISTS dbo.Customers;

In above SQL statement to remove the table, it will check the table exists into the database first before remove it. If it exists then it will remove the table from the database and display SQL statement executed message. If table doesn’t exist into the database, it will execute the SQL statement successfully without table deleted.

Conclusion:

In this tutorial, you have learned about how to remove single or multiple statement in SQL server database by using DROP TABLE SQL statement.