How to drop a column which is having Default constraint in SQL Server 2008?
My query is
alter table tbloffers
drop column checkin
I am getting below error
ALTER TABLE DROP COLUMN checkin failed because one or more objects access this column.
Can anyone correct my query to drop a column with constraint?
ベストアンサー1
First you should drop the problematic DEFAULT constraint
, after that you can drop the column
alter table tbloffers drop constraint [ConstraintName]
go
alter table tbloffers drop column checkin
But the error may appear from other reasons - for example the user defined function or view with SCHEMABINDING
option set for them.
UPD: Completely automated dropping of constraints script:
DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
SELECT TOP 1 @sql = N'alter table tbloffers drop constraint ['+dc.NAME+N']'
from sys.default_constraints dc
JOIN sys.columns c
ON c.default_object_id = dc.object_id
WHERE
dc.parent_object_id = OBJECT_ID('tbloffers')
AND c.name = N'checkin'
IF @@ROWCOUNT = 0 BREAK
EXEC (@sql)
END