How to Safely Drop and Recreate a Table If It Exists in SQL Server

by

Sometimes you need to rebuild a table from scratch. This will usually involve dropping the table and recreating it. Maybe it’s for a schema update, or maybe it’s for reloading data in a staging environment. Whatever the reason, SQL Server provides a straightforward way to handle this.

While we can certainly go right ahead and drop the table, what if it doesn’t actually exist? In this case we’ll get an error. That is unless we take measures to prevent such an error. Such measures will involve checking for the existence of the table before attempting to drop it.

But this leads us to another question; What to do if the table doesn’t already exist? We have a choice:

  • Create it
  • Don’t create it

The code we use will depend on what we want to do in that scenario. In this article we’ll look at each of these options.

Option 1: Always Recreate the Table (if it doesn’t exist)

The first approach drops the table if it exists, then recreates it. But it also creates the table even if it didn’t already exist. This is common in deployment or initialization scripts, where you want the table to exist in a consistent form no matter what.

With SQL Server 2016 and later, the syntax is clean:

DROP TABLE IF EXISTS dbo.SalesData;

CREATE TABLE dbo.SalesData
(
    SalesID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Quantity INT,
    SaleDate DATE
);

Here, DROP TABLE IF EXISTS ensures the old version is removed if it exists, and the CREATE TABLE ensures the new version is always built, even if nothing was dropped. Importantly, it doesn’t try to drop the table if it doesn’t exist (and therefore, it won’t produce an error due to trying to drop a non-existent table).

For SQL Server 2014 and earlier, you can use OBJECT_ID() in a conditional statement to check for the table’s existence instead:

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

CREATE TABLE dbo.SalesData
(
    SalesID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Quantity INT,
    SaleDate DATE
);

This guarantees the table exists in the end, whether freshly created or recreated.

Option 2: Only Recreate if the Table Already Exists

Sometimes you don’t want to create new tables where they don’t already exist. For example, maybe your script runs against multiple databases and you only want to rebuild SalesData if it’s already part of the schema. In that case, wrap both the drop and create inside a check:

IF OBJECT_ID('dbo.SalesData', 'U') IS NOT NULL
BEGIN
    DROP TABLE IF EXISTS dbo.SalesData;

    CREATE TABLE dbo.SalesData
    (
        SalesID INT PRIMARY KEY,
        ProductName NVARCHAR(100),
        Quantity INT,
        SaleDate DATE
    );
END

Now nothing happens if the table isn’t already there.

In this example I used DROP TABLE IF EXISTS even though I already checked for the existence of the table. This guards against the unlikely event that the table is dropped or created in the gap between the OBJECT_ID() check and the DROP TABLE statement. You could probably remove the IF EXISTS if you want, but why not keep it. Of course, if you’re using a pre-2016 version of SQL Server, then you’ll have no option but to omit it.

What if You Need to Keep the Data?

Dropping a table removes both the structure and the rows inside it – along with all data. If you need the data but want to adjust the table definition, you need a safer process. This could look something like the following.

Copy the data into a temporary table:

SELECT * INTO dbo.SalesData_Backup
FROM dbo.SalesData;

Drop and recreate the original table:

DROP TABLE IF EXISTS dbo.SalesData;

CREATE TABLE dbo.SalesData
(
    SalesID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Quantity INT,
    SaleDate DATE,
    Discount DECIMAL(5,2) NULL
);

Reload the data:

INSERT INTO dbo.SalesData (SalesID, ProductName, Quantity, SaleDate)
SELECT SalesID, ProductName, Quantity, SaleDate
FROM dbo.SalesData_Backup;

Drop the backup once you’re confident that the recreation took place without issue:

DROP TABLE dbo.SalesData_Backup;

This approach lets you restructure the table while keeping existing data intact. You may need to adjust the insert step if columns changed or new constraints exist.

When to Use Each Approach

You might decide to develop scripts for different scenarios. The script you use will depend on the scenario:

  • Option 1 (Always create): Good for deployment scripts where you want the table to exist no matter what.
  • Option 2 (Only recreate if exists): Good for scripts running across different databases or environments, where you don’t want to introduce new tables unexpectedly.
  • Backup and reload data: Essential when you want to maintain any existing data.

Watch Out for Dependencies

Dropping a table doesn’t happen in isolation. If other objects like foreign keys, views, or stored procedures depend on that table, your drop will fail. Before dropping, you can check dependencies like this:

SELECT 
    referencing_object_name = o.name,
    referencing_object_type_desc = o.type_desc
FROM sys.sql_expression_dependencies d
JOIN sys.objects o ON d.referencing_id = o.object_id
WHERE d.referenced_entity_name = 'SalesData';

This gives you a quick view of what might break if the table is removed.

Alternative to Dropping: Truncate

Sometimes you don’t actually need to drop and recreate the table. Maybe you just need to clear out the rows. In that case, TRUNCATE TABLE could be a safer choice because it keeps the schema and dependencies intact:

TRUNCATE TABLE dbo.SalesData;

This wipes the data but leaves the table structure exactly as it is.

Altering the Table Without Dropping It

Everything we discussed above assumes that you can’t (or don’t want to) modify the table without dropping it. In most cases you’ll be able to modify the existing table without dropping it or removing any data.

To modify the table without dropping it, see SQL ALTER TABLE For Beginners.