How do you drop a default value from a column in a table?

Its a default constraint, you need to perform a:

ALTER TABLE {TableName} 
DROP CONSTRAINT ConstraintName

If you didn't specify a name when you created the constraint, then SQL Server created one for you. You can use SQL Server Management Studio to find the constraint name by browsing to the table, opening its tree node, then opening the Constraints node.

If I remember correctly, the constraint will be named something along the lines of DF_SomeStuff_ColumnName.

EDIT: Josh W.'s answer contains a link to a SO question that shows you how to find the auto generated constraint name using SQL instead of using the Management Studio interface.

This is what I came up with (before seeing Josh W. answer, well actually I saw it but skimmed it so fast I misunderstood it):

declare @name nvarchar(100)
select @name = [name] from sys.objects where type = 'D' and parent_object_id = object_id('sometable')

if (@name is not null)
  begin
     exec ('alter table [sometable] drop constraint [' + @name +']')
  end

The advantage I have here is that I know that there is only one such constraint on the whole table. If there had been two, well I guess that is why you are supposed to name them ;).

(The issues is that that this is a modification made to 10 different customer databases, so there isn't one consistent name to put in a script)

If you not know of constraints 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)

https://stackoverflow.com/a/13715343/2547164

How do you drop a default value from a column in a table?

Related

Recent Posts