Drop Column

[Ladyhawk]

I can't drop a particular column in a table because it has a default value. So how can I drop the default value via SQL?

Ladyhawk.
** ASP/VB/Java Programmer **

 

[SonOfEmidec1100]

You can drop a default value (contraint) using

ALTER TABLE MyTable DROP CONSTRAINT {constraint name}

 

[Ladyhawk]

But I don't know the constraint associated with a default value.

Ladyhawk.
** ASP/VB/Java Programmer **

 

[SonOfEmidec1100]

This is based on an article in BOL "Working with Default Constraints" worth a read

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) = 'MyTable'
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id('dbo.MyTable') AND
name = 'MyColumn'))
SET @cmd = 'ALTER TABLE MyTable DROP CONSTRAINT '
+ @defname
EXEC(@cmd)

ALTER TABLE MyTable DROP Column MyColumn