How to check if a SQL Server table exists before delete?

Blue and white cubes structure

How to check if a SQL Server table exists before delete? How to drop it only if the existence check shows it already exists in the database to avoid errors, using the DROP TABLE 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. To drop a SQL Server table without error, simply use this code.

How to check if a SQL Server table exists before delete?

How to avoid and fix this SQL Server Error?

« 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 followed by the table name, the DBMS system is throwing an error because the table does not exist. The solution is to test the existence of the table using a simple SQL query.

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 how to Drop a SQL Server table only if it exist. Check first in the objects system table of the SQL Server database, after this drop the table without any error. Indeed if the table does not exists then the DROP TABLE command is simply skipped.

Here is a SQL script to create a SQL Server table from the table management series of articles from the blog.

To go further and learn more details on the syntaxes and options, check this article on the drop table command from the official SQL Server documentation.

Be the first to comment

Leave a Reply

Your email address will not be published.


*