Hi,
I am executing a stored proc. Within the proc a constraint has been created on a Temp Table like -
'ALTER table #EmployeeLedger add constraint ' + @Constraint + ' PRIMARY KEY CLUSTERED (EmployeeId, YrMnth)'.
Here
#EmployeeLedger is the temp table and constraint is created dynamically depending on EmployeeID. I am getting error for a particular record (say 700918). The error says
Msg 2714, Level 16, State 4, Line 1
There is already an object named 'PK_TMP_EMPLED191700918' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
I can understand the constraint is already there. But how can I delete the constraint? I search the 'sys.objects' table but I could not find the 'PK_TMP_EMPLED191700918' object there. My query for search the object is:
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE OBJECT_NAME(OBJECT_ID) LIKE 'PK%' order by OBJECT_ID
but it is not giving any matching result for constraint 'PK_TMP_EMPLED191700918'. So I can not delete the object.
Any help will be thankfully accepted.
Partha