In SQL Server, the DROP TABLE command is a straightforward way to delete a table. This blog post focuses on demonstrating the use of the DROP TABLE
command with the IF EXISTS
argument, a feature available in SQL Server 2016 and later.
The DROP TABLE IF EXISTS
statement allows for the conditional removal of a table. If the specified table exists, it is dropped; otherwise, no error is generated, making it a handy tool for database management.
Drop Table if Exists SQL Server Example
Let’s walk through a practical example. First, we create a sample table using the CREATE TABLE command:
-- Create table SQL Server CREATE TABLE demoTable (r_id INT, r_name VARCHAR(100), r_description VARCHAR(200));
Now, we use the DROP TABLE IF EXISTS
command to delete the table. Remarkably, even if the table is not present, no execution error occurs:
-- Drop table if exists SQL Server DROP TABLE IF EXISTS demoTable;
![SQL Drop Table if Exists](https://dbasco.com/wp-content/uploads/2024/01/mssql-drop-table-if-exists.png)
Running the same command again won’t trigger any issues, if we do this as a regular DROP TABLE
statement an error will show.
![SQL Server Drop Table Error](https://dbasco.com/wp-content/uploads/2024/01/sql-server-drop-table-error-example.png)
Msg 3701, Level 11, State 5, Line 13 Cannot drop the table 'demotable', because it does not exist or you do not have permission.
Verifying a Table Exists in SQL Server
An alternative approach involves checking the existence of a table using system tables. This query checks the sys.tables system table for the presence of a table named ‘demoTable’. If the table exists, it prints a message indicating its existence; otherwise, it prints a message indicating that the table does not exist. You can modify this query based on your specific needs.
-- Verifying a table exists SQL Server IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'demoTable') BEGIN -- Table exists, you can perform actions accordingly PRINT 'Table demoTable exists'; END ELSE BEGIN -- Table does not exist, you can create the table or take other actions PRINT 'Table demoTable does not exist'; END
![](https://dbasco.com/wp-content/uploads/2024/01/sql-server-check-if-a-table-exists.png)