sql server drop default syntax error

Question

I am trying to drop the default value on a bit column, I have set it wrong and I want to do it again correctly. However, when I write:

ALTER TABLE Person
ALTER COLUMN tsHomePref DROP DEFAULT;

I get a 'incorrect syntax near keyword default error' and I don't know why

I want to drop the column and then build it again

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

So, why won't it let me 'drop' the default value?

Thanks R.

Answer 1

You would need to do

ALTER TABLE Person 
    DROP CONSTRAINT DF__Person__tsHomePr__05BA7BDB

It helps if you use a consistent naming convention for these so you don't have to look in the system tables to get the name first.

Answer 2

If you don't know the name of the constraint ( as I didn't know today when searching for a solution to the same problem ), you can use the following for 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 

This worked in my case. Hope it helps others as well.