Menu & Search

Drop Table if Exists in SQL Server

Drop Table if Exists in SQL Server

The DROP TABLE command in SQL Server will do as it suggests and drops a table. This post is to show an example of using this command, including the IF EXISTS argument.

DROP TABLE IF EXISTS in MS SQL allows us to conditionally drop the table, meaning it does not cause an error if the database does not exist.

DROP TABLE IF EXISTS works on SQL Server 2016 and above.
For more information on this, as ever you should check out the Microsoft Documentation on anything and everything you do on their products.

In the following example, I’m creating a new table followed by running the DROP TABLE IF EXISTS command. I run the DROP statement twice to show & explain the execution error part.

# create a test table ms sql
CREATE TABLE demoTable (r_id INT, r_name VARCHAR(100), r_description VARCHAR(200));

# drop the above table
DROP TABLE IF EXISTS T1;

# drop the table again, no execution errors / script will continue
DROP TABLE IF EXISTS T1;
Drop Table if Exist MS SQL

You can also get information from system tables to verify if a SQL Object exists or not. For example, having an IF statement based on a sys.tables query to check if the table exists, before running the create table statement.

0 Comments