How to fix "There is already an object named ' ' in the database" error in sql server

Question

I have created this table, I can't enter data manually because of this error.

    USE [Butterfly]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[VM_Vehicles](
        [VehicleID] [nvarchar](100) NOT NULL,
        [VehicleType] [nvarchar](100) NULL,
        [RegistrationNo] [nvarchar](100) NULL,
        [PurchaseDate] [date] NULL,
        [Make] [nvarchar](100) NULL,
        [Model] [nvarchar](100) NULL,
        [ChassisNo] [nvarchar](100) NULL,
        [EngineNo] [nvarchar](100) NULL,
        [EngineCapacity] [nvarchar](100) NULL,
        [YearofManufacture] [nvarchar](100) NULL,
        [SeatingCapacity] [nvarchar](100) NULL,
        [ContactName] [nvarchar](100) NULL,
        [Phone] [nvarchar](50) NULL,
        [VendorID] [int] NOT NULL,
        [Picture] [image] NULL,
        [VoucherNo] [int] NOT NULL,

     CONSTRAINT [PK_VM_Vehicles1] PRIMARY KEY CLUSTERED 
    (
        [VehicleID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

I have tried using this code to find what's wrong with my database. so far no luck finding error.

IF object_id("tempdb..#VM_Vehicles") is not null
DROP TABLE #VM_Vehicles
CREATE TABLE #VM_Vehicles (vehicleID nvarchar(100), ...);

I already tried changing constraint name and table name. That didn't provide me a answer either.

Regarding how to create a table only if it does not exist, [read this SO question](https://stackoverflow.com/questions/167576/check-if-table-exists-in-sql-server). As for your error, there could be a number of reasons why you have stale objects. — Tim Biegeleisen, Nov 21 '18 at 10:52
`#VM_Vehicles` <> `[dbo].[VM_Vehicles]`. One is a temporary table, and another is a persistant table. — Thom A, Nov 21 '18 at 10:58

StepUp · Answer 1 · 2018-11-21T12:37:46.547

You are creating a persistent table VM_Vehicles in database Butterfly. However, you are checking a temporary table #VM_Vehicles in database TempDB:

IF object_id("tempdb..#VM_Vehicles") is not null

So you are checking another table from another database and so you have a such error:

There is already an object named ' ' in the database

The correct check statement should look like this:

USE Butterfly

IF OBJECT_ID("VM_Vehicles") IS NOT NULL DROP TABLE VM_Vehicles

CREATE TABLE [dbo].[VM_Vehicles](VehicleID nvarchar(100), ...);

How to fix "There is already an object named ' ' in the database" error in sql server

1 Answers1