Wednesday, January 23, 2013

Msg 5074, Level 16, State 1, Line 1


unable to drop column

Msg 5074, Level 16, State 1, Line 1

The object ' ' is dependent on column ' '.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN   failed because one or more objects access this column.

THIS error because of you are trying to drop column , without dropping the constraint.

solution : drop constraint first then column

declare @query nvarchar(1000)
select @query = 'ALTER TABLE ' + t.name + ' drop constraint ' + d.name
from sys.tables t join sys.default_constraints d
on d.parent_object_id = t.object_id
join sys.columns c
on c.object_id = t.object_id and c.column_id = d.parent_column_id
where t.name = 'table_name' and c.name = 'column_name'
execute (@query)

  note : replace table_name and column_name with present table_name and column_name

No comments:

Post a Comment

web stats