Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Drop Column that has a Default value

 
   Database Forums (Home) -> Client RSS
Next:  Documentation on the undocumented CCFLAGS paramet..  
Author Message
Daniel Mauric

External


Since: Apr 24, 2006
Posts: 3



(Msg. 1) Posted: Mon Apr 24, 2006 9:55 pm
Post subject: Drop Column that has a Default value
Archived from groups: microsoft>public>sqlserver>clients (more info?)

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 ?

Regards,
Danny

 >> Stay informed about: Drop Column that has a Default value 
Back to top
Login to vote
Hugo Kornelis

External


Since: Jan 31, 2006
Posts: 335



(Msg. 2) Posted: Mon Apr 24, 2006 9:55 pm
Post subject: Re: Drop Column that has a Default value [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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

 >> Stay informed about: Drop Column that has a Default value 
Back to top
Login to vote
Daniel Mauric

External


Since: Apr 24, 2006
Posts: 3



(Msg. 3) Posted: Mon Apr 24, 2006 10:55 pm
Post subject: Re: Drop Column that has a Default value [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Thanks for the info, it did help

Regards,
Danny
 >> Stay informed about: Drop Column that has a Default value 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
DROP COLUMN - If I execute ALTER TABLE DROP COLUMN XX but the column XX has Constraints there will be an error. I should first delete the related Constraints. Is there any way to delete all of the Constraints related to this column? Should I navigate before for al...

Drop column in transaction - I perform a DROP COLUMN and it works fine. Yet if I do it while in transaction the column doesn't get dropped and I get no exception. Any ideas as to why that would happen ? I'm using ADO & MSSQL 2k Regards, Danny

drop and create in the same script in management studio - In SQL 2000, we could generate the drop and create statements for an object in the same script. I have not been able to do this in management studio. Am I missing something?

DEFAULT INSTANCE INSTALLATION PROBLEM ON SQL2005 - I have win2003 machine which is named fenermarket. I have an installed instance which is FENERMARKET\ALISVERISSAATI. I installed sp1 and sp2 too. When i try to install default instance as another instance the setup process give the error.Im sure that...

'[' in column name - I'm running a OpenRowset query getting data from MSAS (MSOLAP). The query returns column names such as '[Time Period].[BTG][Measures].[FC]'. I need to create an alias for this column. How could I do it? I tried '[[Time Period].[BTG][Measures].[FC]]',....
   Database Forums (Home) -> Client All times are: Pacific Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]