0

I tried following query to check whether constraint exists for a table or not.

IF OBJECT_ID('SET_ADDED_TIME_AUTOMATICALLY') IS NOT NULL
ALTER TABLE HS_HR_PEA_EMPLOYEE DROP CONSTRAINT SET_ADDED_TIME_AUTOMATICALLY

Got this error.

Constraint 'SET_ADDED_TIME_AUTOMATICALLY' does not belong to table 'HS_HR_PEA_EMPLOYEE'

Then I tried to add the constraint to the table.

ALTER TABLE HS_HR_PEA_EMPLOYEE
ADD CONSTRAINT SET_ADDED_TIME_AUTOMATICALLY DEFAULT GETDATE() FOR JS_PICKED_TIME

Got this one.

There is already an object named 'SET_ADDED_TIME_AUTOMATICALLY' in the database.

What's wrong with the query. Do I have to mention table name with the schema?

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
Nishantha
  • 6,065
  • 6
  • 33
  • 51

1 Answers1

3
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 type_desc LIKE '%CONSTRAINT'
    AND OBJECT_NAME(OBJECT_ID)='SET_ADDED_TIME_AUTOMATICALLY'
Backs
  • 24,430
  • 5
  • 58
  • 85