SQL Server If Object_ID Is Not Null Drop Table

As an SQL developer, I often need to drop a table based on certain conditions. After my analysis, I identified that OBJECT_ID IS NOT NULL DROP TABLE, which is a great way to achieve this. In this article, I will walk you through the complete steps to use this pattern.

SQL Server If Object_ID Is Not Null Drop Table

Let us discuss all the approaches individually.

Approach-1: Using a simple If statement

Syntax

IF OBJECT_ID('dbo.TableName', 'U') IS NOT NULL 
    DROP TABLE dbo.TableName;

Example

Let us see a real-time example where we will try executing a table HomeUSA if that exists.

Just for your information, the table is already present. See the screenshot below for confirmation.

Now, let us run the below query to check for the table and delete it if it exists.

IF OBJECT_ID('dbo.HomeUSA', 'U') IS NOT NULL 
    DROP TABLE dbo.HomeUSA;

The command was completed successfully after executing the above query, as shown in the screenshot below.

To cross-check if the table was deleted successfully, let us execute the below select query.

Select * from HomeUSA

It shows “Invalid object name ‘HomeUSA'” as the table doesn’t exist now. Check out the screenshot below for your reference.

If you wish to drop multiple tables simultaneously, you can execute the queries below anytime.

IF OBJECT_ID('dbo.Product', 'U') IS NOT NULL 
    DROP TABLE dbo.Product;
IF OBJECT_ID('dbo.Vehicle', 'U') IS NOT NULL 
    DROP TABLE dbo.Vehicle;

Check out How To Drop Temp Table If Exists In SQL Server

Approach-2 Using Variables inside the If statement.

You need to specify the query as shown below. Here, we are checking for the USAHome table and deleting it if it exists.

DECLARE @MyTableName NVARCHAR(101) = 'USAHome';
DECLARE @MyTableSchemaName NVARCHAR(101) = 'dbo';

IF OBJECT_ID(@MyTableSchemaName + '.' + @MyTableName, 'U') IS NOT NULL 
BEGIN
    DECLARE @mySQL NVARCHAR(MAX) = 'DROP TABLE ' + @MyTableSchemaName + '.' + @MyTableName;
    EXEC sp_executesql @mySQL;
END

After executing the above query, the command executed, and the USAHome table was deleted successfully, as shown in the screenshot below.

Now, to cross-check if the table was deleted successfully, I executed the select query below. The result shows an invalid object name, which means the table was deleted successfully, as shown in the screenshot below.

Conclusion

OBJECT_ID IS NOT NULL DROP TABLE pattern is a great way to clear up unused tables in seconds. Utilize any of the approaches mentioned in this article to achieve this.

You may also like following the articles below.

• How To Drop All Constraints On A Table In SQL Server
• SQL Server Remove Duplicates From A Table
• How to Find Table Dependencies in SQL Server
• How To Change Schema Of A Table In SQL Server