Re: Drop Column that has a Default value
- From: Hugo Kornelis <hugo@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 24 Apr 2006 23:27:00 +0200
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
.
- Follow-Ups:
- Re: Drop Column that has a Default value
- From: Daniel Mauric
- Re: Drop Column that has a Default value
- References:
- Drop Column that has a Default value
- From: Daniel Mauric
- Drop Column that has a Default value
- Prev by Date: Drop Column that has a Default value
- Next by Date: Re: query analyzer mru
- Previous by thread: Drop Column that has a Default value
- Next by thread: Re: Drop Column that has a Default value
- Index(es):
Relevant Pages
|
