SQL Server DELETE or DROP Table

SQL Server allows DBA to remove a table, which is not relevant or obsolete in our database. When we remove a table, the complete data and the whole structure or definition are deleted permanently from the table. Therefore, we should have to be very careful while dropping a table from the specified database. If the SQL Server does not find the target database table, it will throw an error message.

SQL Server provides mainly two ways to remove a table:

• Transact-SQL Command
• SQL Server Management Studio

Let us discuss them one by one in detail.

DROP TABLE using T-SQL command

We can use the following syntax to remove a table in SQL Server:

In the above syntax,

• We need first to write the table name that we want to remove. Next, we need to specify the database name in which the table is stored, and the schema name indicates to which this table belongs. It should note that the database name is optional. If we omit this, this query will remove the table from the currently connected database.
• The IF EXIST is an optional clause indicating the table will be deleted only if it already exists in the database. This clause is available since SQL Server 2016 13.x.

If we want to delete more than one table within a single command, we can use the comma-separated list of table names as below:

When we remove a table in SQL Server, it also erases all of the table's data, triggers, constraints, and permissions. This query does not allow deletion of the views, user-defined functions, and stored procedures explicitly referencing the dropped table. Hence, we should use the DROP VIEW and DROP PROCEDURE statements if we want to remove these objects explicitly.

We must remember the following points before deleting a table:

1. The DROP TABLE query will delete the table, including its physical disc files. Therefore, we must keep a backup if we need to recover it in the future.
2. If a foreign key constraint references the table, this query does not drop a table. If we want to do this, we will first remove the referencing foreign key constraint or the table. Also, we first list the referencing table in an event when the reference table and the primary key table are both being deleted in the same DROP TABLE statement.
3. This statement does not remove the data stored in the file system when the table has a varbinary (max) column with the FILESTREAM

NOTE: We must have an ALTER privilege on the schema to which the table belongs and CONTROL privilege on the table to execute the DROP TABLE statement.

Example

This example specifies how we can drop an existing table from the database. Suppose our database has a table named "student_data" containing the following data:

If we want to drop this table from the database, we can use the statement as follows:

It will remove the table permanently. If we re-access this table, we will get an error message as shown in the below output:

If we try to remove a table that does not exist in the database, SQL Server will through the following error message:

However, if we try to delete a table that is not available in the database with the IF EXISTS clause, SQL Server does not show any error or warning message. See the below output:

DROP TABLE using SQL Server Management Studio

We can also use the SSMS to drop a table from the database. The following steps explain how to delete a table using SSMS:

Step 1: Open the SSMS and then navigate to the Object Explorer-> Database -> Tables folder. It will show all available tables.

Step 2: Select your desired table that is no longer required in your database and right-clicks on it to get the pop menu. Now, select the Delete option from the appeared menu. See the below image:

Step 3: Once we click on the Delete option, we will see the Delete Model Object box to confirm the deletion process. Click on the OK button to delete the database from the SQL Server permanently.

Step 6: If everything is correct, the selected table will be removed from the database. We can verify this by clicking the refresh button or pressing the F5 on our keyboard to update the object list. We can see that the 'backup_employee' table is no longer available.

This article helps to learn how to remove the table from a database permanently using SQL query and SQL Server Management Studio.