Default syntax error for sql server

Question

I am trying to reset the default value in the bit column, I set it incorrectly and I want to do it again correctly. However, when I write:

ALTER TABLE Person
ALTER COLUMN tsHomePref DROP DEFAULT;

I get "incorrect syntax next to default error by default" and I don't know why

I want to delete a column and then create it again

ALTER TABLE Person
ADD COLUMN tsHomePref bit NOT NULL DEFAULT 0;

So why doesn't he let me “delete” the default value?

Thanks R.

Answer 1

You will need to do

ALTER TABLE Person 
    DROP CONSTRAINT DF__Person__tsHomePr__05BA7BDB

This helps if you use a consistent naming convention for them, so you don't need to search the system tables to get the name first.

Answer 2

( ), Ms Sqlserver:

DECLARE @defname VARCHAR(100), @cmd VARCHAR(1000)
SET @defname =
   (SELECT name FROM sysobjects so
     JOIN sysconstraints sc ON so.id = sc.constid
   WHERE object_name(so.parent_obj) = 'TableName'
     AND sc.colid = 
         (SELECT colid FROM syscolumns WHERE id = object_id('dbo.TableName') AND name = 'FieldName'))
    SET @cmd = 'ALTER TABLE TableName DROP CONSTRAINT ' + @defname
   EXEC(@cmd)
GO 

. , .