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:
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,
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:
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.
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.