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.
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;
Running the same command again won’t trigger any issues, if we do this as a regular
DROP TABLE statement an error will show.
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