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.

possible duplicate of [How do you drop a default value or similar constraint in T-SQL?](http://stackoverflow.com/questions/1123060/how-do-you-drop-a-default-value-or-similar-constraint-in-t-sql) — Ruben Bartelink, Nov 01 '13 at 10:55

score 5 · Accepted Answer · answered Jun 29 '10 at 00:08

5

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.

answered Jun 29 '10 at 00:08

Martin Smith

438,706
87
741
845

Yep, this worked and I was able to add the column with the default value that I wanted. Thanks. – flavour404 Jun 30 '10 at 17:01

score 4 · Answer 2 · answered Jan 27 '11 at 15:08

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.

sql server drop default syntax error

2 Answers2