// SQL Server error reference

SQL Server error 2714: There is already an object named

CREATE TABLE, VIEW, PROCEDURE or a constraint uses a name that already exists in the schema. Object and constraint names share one namespace.

Error number
2714
Severity
16
Category
Names and objects
Error message
There is already an object named '%.*ls' in the database.

What it means

Within a schema, tables, views, procedures, functions and constraints (primary keys, foreign keys, defaults, checks) all share one namespace. Creating an object whose name is already taken fails with 2714. Temp tables raise it too when a #temp of that name already exists in the session.

Common causes

  • Running a deployment script twice without a guard.
  • A constraint name that matches another object, for example two tables each declaring a primary key named PK_Id.
  • SELECT ... INTO #t or CREATE TABLE #t when #t still exists from an earlier run in the same session.
  • Two CREATE TABLE #t statements in the same batch or procedure, even in different IF branches.

How to fix it

  1. Use CREATE OR ALTER for views, procedures, functions and triggers (SQL Server 2016 SP1 and later).

    CREATE OR ALTER VIEW dbo.ActiveCustomers AS
    SELECT CustomerId, Name FROM dbo.Customers WHERE IsActive = 1;
  2. Drop first with DROP ... IF EXISTS (SQL Server 2016 and later), including for temp tables.

    DROP TABLE IF EXISTS #work;
    SELECT * INTO #work FROM dbo.Orders WHERE 1 = 0;
  3. Find what owns the name.

    SELECT SCHEMA_NAME(schema_id) AS schema_name, name, type_desc, OBJECT_NAME(parent_object_id) AS parent
    FROM sys.objects WHERE name = N'PK_Id';
  4. Give constraints table-specific names, such as PK_Orders and FK_Orders_Customers.

Related errors

More names and objects errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.