SQL Server Drop Table only if exists

Blue and white cubes structure

How to, with SQL Server Drop Table only if exists? How to drop it only if it already exists in the database to avoid errors, use the DROP keyword. Indeed when running long SQL scripts or running scripts twice, it is much better to test the table existence. Indeed, we highly recommend testing the table existence to avoid the following error message.

« Msg 3701, Level 11, State 5, Line 1
Cannot drop the table ‘SALES’, because it does not exist or you do not have permission. »

You want to DROP a table from your SQL Server database, but using directly the SQL command DROP TABLE, the DBMS system is throwing an error because the table is not there yet. The solution is to test the existence of the table using a simple SQL query.

With SQL Server, DROP Table if exists

Solution: Test the SQL Server table existence before dropping it with the IF EXISTS and DROP functions.

IF EXISTS (
  SELECT 1 FROM sys.objects
  WHERE object_id = object_id(N'[dbo].[SALES]')
    AND type in (N'U') 
)
BEGIN
  DROP TABLE [dbo].[SALES]
END;

This post shows an SQL query to DROP a table if exists in SQL Server, and only if it exists in the database, here is a script to create an SQL Server table.

Be the first to comment

Leave a Reply