How to drop column with constraint?

Question

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

Here's another way to drop a default constraint with an unknown name without having to first run a separate query to get the constraint name:

DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID('__TableName__')
AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns
                        WHERE NAME = N'__ColumnName__'
                        AND object_id = OBJECT_ID(N'__TableName__'))
IF @ConstraintName IS NOT NULL
EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)

You can also drop the column and its constraint(s) in a single statement rather than individually.

CREATE TABLE #T
  (
     Col1 INT CONSTRAINT UQ UNIQUE CONSTRAINT CK CHECK (Col1 > 5),
     Col2 INT
  )

ALTER TABLE #T DROP CONSTRAINT UQ , 
                    CONSTRAINT CK, 
                    COLUMN Col1


DROP TABLE #T 

Some dynamic SQL that will look up the names of dependent check constraints and default constraints and drop them along with the column is below

(but not other possible column dependencies such as foreign keys, unique and primary key constraints, computed columns, indexes)

CREATE TABLE [dbo].[TestTable]
(
A INT DEFAULT '1' CHECK (A=1),
B INT,
CHECK (A > B)
)

GO

DECLARE @TwoPartTableNameQuoted nvarchar(500) = '[dbo].[TestTable]',
        @ColumnNameUnQuoted sysname = 'A',
        @DynSQL NVARCHAR(MAX);

SELECT @DynSQL =
     'ALTER TABLE ' + @TwoPartTableNameQuoted + ' DROP' + 
      ISNULL(' CONSTRAINT ' + QUOTENAME(OBJECT_NAME(c.default_object_id)) + ',','') + 
      ISNULL(check_constraints,'') + 
      '  COLUMN ' + QUOTENAME(@ColumnNameUnQuoted)
FROM   sys.columns c
       CROSS APPLY (SELECT ' CONSTRAINT ' + QUOTENAME(OBJECT_NAME(referencing_id)) + ','
                    FROM   sys.sql_expression_dependencies
                    WHERE  referenced_id = c.object_id
                           AND referenced_minor_id = c.column_id
                           AND OBJECTPROPERTYEX(referencing_id, 'BaseType') = 'C'
                    FOR XML PATH('')) ck(check_constraints)
WHERE  c.object_id = object_id(@TwoPartTableNameQuoted)
       AND c.name = @ColumnNameUnQuoted;

PRINT @DynSQL;
EXEC (@DynSQL); 

Find the default constraint with this query here:

SELECT
    df.name 'Constraint Name' ,
    t.name 'Table Name',
    c.NAME 'Column Name'
FROM sys.default_constraints df
INNER JOIN sys.tables t ON df.parent_object_id = t.object_id
INNER JOIN sys.columns c ON df.parent_object_id = c.object_id AND df.parent_column_id = c.column_id

This gives you the name of the default constraint, as well as the table and column name.

When you have that information you need to first drop the default constraint:

ALTER TABLE dbo.YourTable
DROP CONSTRAINT name-of-the-default-constraint-here

and then you can drop the column

ALTER TABLE dbo.YourTable DROP COLUMN YourColumn

The following worked for me against a SQL Azure backend (using SQL Server Management Studio), so YMMV, but, if it works for you, it's waaaaay simpler than the other solutions.

ALTER TABLE MyTable
    DROP CONSTRAINT FK_MyColumn
    CONSTRAINT DK_MyColumn
    -- etc...
    COLUMN MyColumn
GO

I got the same:

ALTER TABLE DROP COLUMN failed because one or more objects access this column message.

My column had an index which needed to be deleted first. Using sys.indexes did the trick:

DECLARE @sql VARCHAR(max)

SELECT @sql = 'DROP INDEX ' + idx.NAME + ' ON tblName'
FROM sys.indexes idx
INNER JOIN sys.tables tbl ON idx.object_id = tbl.object_id
INNER JOIN sys.index_columns idxCol ON idx.index_id = idxCol.index_id
INNER JOIN sys.columns col ON idxCol.column_id = col.column_id
WHERE idx.type <> 0
    AND tbl.NAME = 'tblName'
    AND col.NAME = 'colName'

EXEC sp_executeSql @sql
GO

ALTER TABLE tblName
DROP COLUMN colName