SQLTeam.com | Weblogs | Forums

Cannot find database when trying to create it

USE [mim2]
GO

/****** Object: Table [dbo].[[AppointmentToPayment]] Script Date: 11/21/2020 3:50:11 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS(SELECT 1 FROM sys.tables WHERE name = 'A2P') DROP TABLE A2P

CREATE TABLE [dbo].[A2P] (
[id] nvarchar NOT NULL,
[description] nvarchar NULL,
[comment] nvarchar NULL,
[appointment_id] nvarchar NULL,
[date_of_service] [datetime] NULL,
[CII2_transfer] [datetime] NULL,
[CII2_import] [datetime] NULL,
[charge_id] nvarchar NULL,
[claim_batch_id] nvarchar NULL,
[claim_submitted] [datetime] NULL,
[payment_date] [datetime] NULL,
[total_charge_amount] [money] NULL,
[total_payment_amount] [money] NULL,
[inactive] [bit] NULL,
[entry_date_time] [datetime] NULL,
[update_date_time] [datetime] NULL,
CONSTRAINT [A2P] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Msg 2714, Level 16, State 5, Line 13
There is already an object named 'A2P' in the database.
Msg 1750, Level 16, State 1, Line 13
Could not create constraint or index. See previous errors.

I have looked in Views.sys.all_objects and I do not see this table there. How can I find it to correct this error?

You cannot have the same name for the table and the constraint. Change the line that reads
CONSTRAINT [A2P] PRIMARY KEY CLUSTERED
to something like
CONSTRAINT [pk_A2P] PRIMARY KEY CLUSTERED

2 Likes

I had quickly copied from another example and missed that condition. Thanks,