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
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 #torCREATE TABLE #twhen#tstill exists from an earlier run in the same session.- Two
CREATE TABLE #tstatements in the same batch or procedure, even in differentIFbranches.
How to fix it
-
Use
CREATE OR ALTERfor 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;
-
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;
-
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';
-
Give constraints table-specific names, such as
PK_OrdersandFK_Orders_Customers.
Related errors
1913Index or statistics name already exists15023User, group, or role already exists42P07PostgreSQL: relation already exists1050MySQL: Table already existsORA-00955Oracle: name is already used by an existing object
More names and objects errors
208Invalid object name207Invalid column name4104Multi-part identifier not bound209Ambiguous column name2812Could not find stored procedure3701Cannot drop, object not found3702Cannot drop database, currently in use5030Database couldn't be exclusively locked
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.
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.