Remove default value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Remove default value

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-06-21 : 17:33:57

I have added column_A with the following:

ALTER TABLE features ADD column_A BIT NOT NULL
DEFAULT 0 WITH VALUES

Now, If I want to delete it, I first have to remove the default value of "0" before I can:

TABLE features drop column column_A

How do I remove the defualt value before I drop the column?

Thanks,
Lane

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-21 : 18:16:40

Run sp_help to get the default constraint name

sp_help features

then run your alter table statement and drop constrain and column
(Replace the constraint name with the one you get from sp_help)

ALTER TABLE features
DROP DF__features__column__7D439ABD,COLUMN column_A

HTH
Jasper Smith

lane0618
Posting Yak Master

134 Posts

Posted - 2002-06-21 : 18:28:46

When I run sp_help features I get 9 columns of data about each column but nothing regarding costraints. What am I missing?

Thanks,
Lane

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-21 : 19:47:22

Scroll down the results a bit more

You could also use sp_helpconstraint

sp_helpconstraint features

HTH
Jasper Smith

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-06-22 : 04:33:08

It's not really recommended to query system tables, but try this if it's a one off thingy.

DECLARE @Default sysname
SET @Default = (SELECT OBJECT_NAME(cdefault) FROM syscolumns WHERE id = OBJECT_ID('Features') AND name = 'Column_A')
EXEC ('ALTER TABLE Features DROP CONSTRAINT ' + @Default)
ALTER TABLE Features DROP COLUMN Column_A

--
HTH,
Vyas
http://vyaskn.tripod.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources