SQL SERVER – Fix: There is already an object named ‘MyFirstTable’ in the database

Recently I had a very interesting scenario with a client when they faced an error about already object named  that exist in the database but they were not able to find that particular table in their database. Let us learn about this interesting error and its solution.

During the consultation of Comprehensive Database Performance Health Check my client recently asked if I know how to solve the following error.

Msg 2714, Level 16, State 6, Line 5
There is already an object named ‘MyFirstTable’ in the database.

Diagnosis – Error Object Named

Here is the simplified script they were using to create a table.

CREATE TABLE MyFirstTable
(ID INT NOT NULL);

I asked my client to check in their database as the error said there may be already MyFirstTable in the database. However, they were very sure that there is no such table exists. I asked them to send me the results of the following query.

SELECT *
FROM sys.tables
WHERE name = 'MyFirstTable'

The result of the query above was empty so it is was very clear that there were no such tables. This leads to giving them another query that will look beyond the tables.

SELECT *
FROM sys.objects
WHERE name = 'MyFirstTable'

When I ran the query above it gave me the result with a single row. Let us first look at the result.

It was very clear from the results that there was another object which was not a table. The object was a primary key on another object. In our case, the parent_object_id was 629577281. I was able to get the name of the original table by running the following code.

SELECT OBJECT_NAME(parent_object_id) ParentObject, *
FROM sys.objects
WHERE name = 'MyFirstTable'

Now when I ran the above query, I got the parent object (table name) on which I had a primary key with the name MyFirstTable.

Right after that, I checked the definition of the FirstTable and indeed it contained the primary key with the name MyFirstTable.

I am including the definition of the table over here:

CREATE TABLE FirstTable
(ID INT NOT NULL,
CONSTRAINT MyFirstTable PRIMARY KEY (ID));

Fix/Workaround/Solution:

Now the solution is very simple.

1. Create another table with a different name
2. Rename the original primary key name with a new name

Let me know if you have any questions or concerns about this blog post. You can also reach out to me on Twitter or LinkedIn.

Reference: Pinal Dave (https://blog.sqlauthority.com)