Dropping a table in SQL easy. You simply use DROP TABLE myTable where myTable is the name of the table you want to drop. You can use this method to drop a table in SQL Server via T-SQL script.

But you’ll get an error if the table doesn’t actually exist. That is, unless you check for the existence of the table first.

Below are four ways of using T-SQL to check whether the table exists before dropping it.

Option 1: The IF EXISTS Clause

When using SQL Server 2016 or later, we can use the IF EXISTS clause of the DROP TABLE statement to check for the existence of the table before we try to drop it:

DROP TABLE IF EXISTS t1;

This drops a table called t1 if it exists.

Here’s the result of running the above statement when the table does exist (and is therefore dropped):

Commands completed successfully.

And here’s what we get if we immediately run the statement again (i.e. when the table doesn’t exist):

Commands completed successfully.

Same output. So the statement runs successfully whether the table exists or not.

Here’s what happens if we try to drop the table without using the IF EXISTS clause:

DROP TABLE t1;

Result:

Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 't1', because it does not exist or you do not have permission.

Option 2: Check the Object ID

We can alternatively use the OBJECT_ID() function to check for the existence of the table before we try to drop it:

IF OBJECT_ID(N'dbo.t1', N'U') IS NOT NULL
DROP TABLE dbo.t1;
GO

The above example checks the object ID for a dbo.t1 table. The second argument to OBJECT_ID() specifies the type of object we’re looking for. In this case we use U, which is for “user defined table”.

The OBJECT_ID() returns the database object identification number of a schema-scoped object. If the object exists, it will not return NULL, and therefore, we can drop the table. If the object doesn’t exist, or if you don’t have access to it, the function returns NULL, and the DROP TABLE statement will not run.

Option 3: Query information_schema.tables

Another way to check whether a table already exists is to query the information_schema.tables view:

IF EXISTS (
    SELECT * FROM information_schema.tables
    WHERE table_schema = 'dbo' AND table_name = 't1') 	
    DROP TABLE dbo.t1;

That does a similar thing to the previous example; it checks for the existence of the table and creates it only if it doesn’t exist.

Option 4: Query sys.tables

We can also query the sys.tables system catalog view:

IF EXISTS (
    SELECT * FROM sys.tables t 
    JOIN sys.schemas s ON (t.schema_id = s.schema_id) 
    WHERE s.name = 'dbo' AND t.name = 't1') 	
    DROP TABLE dbo.t1;

SQL Server provides many other ways to check for the existence of tables. See 6 Ways to Check if a Table Exists in SQL Server for examples.