Drop Column that has a Default value

On Mon, 24 Apr 2006 22:52:15 +0200, "Daniel Mauric" <danny at neobee dot
net> wrote:

>Hi all
>
>I'd like to drop a column which has a default value. This fails as the
>default value object is stored in SysObjects. So that needs to be deleted in
>order for drop column to work. Doing this manually works fine, but doing it
>programatically doesn't as delete from SysObjects fails with this message:
>
>"Ad hoc updates to system catalogs are not enabled. The system administrator
>must reconfigure SQL Server to allow this."
>
>I have googled up a tip to use sp_configure to get around the problem, but
>have also read that this doesn't work in MSSQL 2005.
>So I wonder is there a simple solution for dropping a column with defaults
>that works for all MSSQL versions ?

Hi Danny,

First of all: NEVER make changes to system tables if you don't know
exactly what you're doing. You can easily corrupt your DB beyond repair
that way!

In order to drop the column, you have to drop the default "constraint"
first. That's easy if you explicitly chose a good name for the default
constraint when you created it:

CREATE TABLE Test
(Col1 int NOT NULL PRIMARY KEY,
Col2 int NOT NULL CONSTRAINT MyDefault DEFAULT 3
)
go
ALTER TABLE Test
DROP CONSTRAINT MyDefault
ALTER TABLE Test
DROP COLUMN Col2
go
DROP TABLE Test
go

If you didn't specify a name for the default, SQL Server will have
generated it for you. To find the names of existing defaults, use this
query in SQL Server 2005:

SELECT *
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('YourTable')

Or this query in SQL Server 2000 (works in SQL Server 2005 as well, by
the way):

SELECT OBJECT_NAME(constid), *
FROM sysconstraints
WHERE id = OBJECT_ID('Test')
AND status & 5 = 5


--
Hugo Kornelis, SQL Server MVP