MS SQL Server 2000 does not allow columns with Default value constraints to
be altered or dropped. So, is there any way to alteror drop those columns?. The problem
occurs because the DEFAULT clause actually creates a constraint on the
table. This name of this constraint is chosen by SQL Server and will differ
from database to database.

A posible solution wolud be:
exec sp_unbindefault ‘table.column’
does not work but gives us the following error:
Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from ‘table.column’. Use ALTER TABLE DROP CONSTRAINT

Great, use ALTER TABLE DROP CONSTRAINT, but the problem is that we don’t know the
name of the contraint! Makes it kind of hard for us to script this to work
for multiple databases.

The only working solution we have seems very cumbersome.

DECLARE @STR VARCHAR(100)
SET @STR = (
SELECT NAME
FROM SYSOBJECTS SO
JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
WHERE OBJECT_NAME(SO.PARENT_OBJ) = ‘

AND SO.XTYPE = ‘D’ AND SC.COLID =
(SELECT COLID FROM SYSCOLUMNS WHERE ID = OBJECT_ID(”) AND NAME = ”))
SET @STR = ‘ALTER TABLE DROP CONSTRAINT ‘ + @STR EXEC (@STR)

Credits to Joe Geretz

Advertisements