How to Conditionally Drop Objects Before Recreating Them in SQL Server

Recreating objects like tables, views, stored procedures, or functions is quite common when developing databases. Maybe you’re iterating on a design, maybe you’re fixing a bug, or maybe you just need a clean slate. The problem is that SQL Server will throw an error if you try to create an object that already exists. To avoid that, you’ll need a reliable way to conditionally drop the object before recreating it.

And this isn’t just a matter of convenience. It helps keep scripts idempotent, meaning you can run them multiple times without worrying about errors or leftover objects from previous runs.

Fortunately SQL Server provides us with at least two easy options for doing this.

Using DROP IF EXISTS

As of SQL Server 2016, there’s a syntax specifically for conditionally dropping an object if it exists.It goes something like this:

DROP <object> IF EXISTS

It works with most object types – tables, views, stored procedures, functions, and more.

Conditionally Drop Tables

Here’s an example that conditionally drops and creates two tables:

-- Drop and recreate Customers table
DROP TABLE IF EXISTS dbo.Customers;
GO

CREATE TABLE dbo.Customers
(
    CustomerId INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(255) NULL
);
GO

-- Drop and recreate Orders table
DROP TABLE IF EXISTS dbo.Orders;
GO

CREATE TABLE dbo.Orders
(
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT NOT NULL,
    OrderDate DATETIME2 NOT NULL DEFAULT GETDATE(),
    TotalAmount DECIMAL(10,2) NOT NULL,

    CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerId)
        REFERENCES dbo.Customers(CustomerId)
);
GO

That’s all it takes. No conditional logic, no system function calls – just one clean line before the CREATE statement.

That T-SQL code conditionally dropped and recreated the Customers and Orders tables. If any of the tables don’t exist, no error will be thrown when it attempts the drop operation.

Conditionally Drop Stored Procedures

The same pattern also applies to other object types. Here’s an example with a stored procedure:

DROP PROCEDURE IF EXISTS dbo.GetCustomerOrders;
GO

CREATE PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

The script can be run over and over without error. If the procedure exists, it’s dropped. If it doesn’t, SQL Server just skips the drop and moves on.

Conditionally Dropping a View

We can also use the pattern to conditionally drop and recreate views:

DROP VIEW IF EXISTS dbo.vwCustomerOrders;
GO

CREATE VIEW dbo.vwCustomerOrders
AS
SELECT c.CustomerId, c.Name, o.OrderId, o.OrderDate, o.TotalAmount
FROM dbo.Customers c
INNER JOIN dbo.Orders o ON c.CustomerId = o.CustomerId;
GO

This view joins both of the tables we created earlier.

The Old School Way: OBJECT_ID() Checks

If you’re working in SQL Server 2014 or earlier, you won’t have DROP IF EXISTS. In that case, the common approach is to use OBJECT_ID() to check whether the object exists. For example, here’s how we can conditionally drop and recreate the stored procedure from earlier:

IF OBJECT_ID('dbo.GetCustomerOrders', 'P') IS NOT NULL
    DROP PROCEDURE dbo.GetCustomerOrders;
GO

CREATE PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

In this case, I provided P as the second argument. This specifies the object type code (P for procedure, U for table, V for view, etc).

It’s not quite as concise as the DROP IF EXISTS statement, but it’s certainly a valid option.

One benefit of OBJECT_ID() is that it can also be used to create the object only if it doesn’t exist without dropping it if it does exist. In this case, you can be assured that you won’t accidentally drop the wrong object (and potentially destroy important data in the process).