If you’re getting SQL Server error 2714 that reads something like “There is already an object named ‘Actors’ in the database” it seems that you’re trying to create an object that already exists.

It could be that you didn’t know that the object had already been created. Or it could be that you’re trying to create a different object, but you’re inadvertently using the same name that another object has. Or it could be that you want both objects to have the same name, but a different schema. In this case, perhaps you inadvertently omitted the schema name from the object.

Either way, in order to fix this issue, you’ll need to make sure that you’re not using a name that clashes with another object in the database.

Example of Error

Here’s an example of code that produces the error:

CREATE TABLE Actors (
    ActorID INT PRIMARY KEY,
    Name VARCHAR(50),
    BirthYear INT NULL
);

Result:

Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Actors' in the database.

Here’s another one:

CREATE VIEW Actors AS 
SELECT * FROM Actors;

Output:

Msg 2714, Level 16, State 3, Procedure Actors, Line 1
There is already an object named 'Actors' in the database.

And here’s another one:

CREATE SYNONYM actors
FOR actors;

Output:

Msg 2714, Level 16, State 8, Line 1
There is already an object named 'actors' in the database.

In all three cases, I’m trying to create an object with the same name of an object that exists in the database, and that’s why I’m getting the error.

Solution 1

If we’re getting the error because we’re trying to create the same object that already exists (perhaps we didn’t know that it already existed), then we could check whether it already exists first.

For example, regarding the first example, we can check whether the table exists before creating it:

IF OBJECT_ID(N'dbo.Actors', N'U') IS NULL
CREATE TABLE Actors (
    ActorID INT PRIMARY KEY,
    Name VARCHAR(50),
    BirthYear INT NULL
);

Output:

Commands completed successfully.

Bear in mind that this doesn’t do anything if the object already exists – it doesn’t create anything. But it also doesn’t produce an error, which could be helpful, depending on the situation.

It’s worth nothing that some other RDBMSs support an IF NOT EXISTS clause, which can be included right into the CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS Actors (
    ActorID INT PRIMARY KEY,
    Name VARCHAR(50),
    BirthYear INT NULL
);

At the time of this writing, SQL Server doesn’t support that clause, and so we have to settle for a method like the previous one.

Solution 2

Another way to fix this problem is to simply use a different name for the new object that we’re trying to create.

For example:

CREATE VIEW vActors AS 
SELECT * FROM Actors;

Output:

Commands completed successfully.

Here, I prefixed the view name with v, making it vActors instead of just Actors. Now I’ve got a table called Actors and a view called vActors.

Solution 3

Another way to address the issue is to use a different schema (and to specify it).

For example:

CREATE SYNONYM Application.actors
FOR dbo.actors;

Output:

Commands completed successfully.

In this case I created the actor synonym under the Application schema, and this references the actor table in the dbo schema.