how to delete a default definition of a column

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)

how to delete a default definition of a column

Author

Topic

chorofonfilo
Starting Member

40 Posts

Posted - 2008-01-29 : 14:33:43

Hello everyone there, and thank you very much for your help in advance.
I created a table having one of its columns with a default definition without a name, set with a initial value of 0, like this:

Create table requests(
reqid int,
reqname varchar(30),
imprchk int default 0
)

Now i wanna get rid of the default definition for this column so i can actually delete the column imprchk without deleting the whole table.

I tried ussing the sp_unbindefault system procedure, but it tells me that i do need to use alter table drop constraint to make it, when i try is still not working.

Does anyone would help me on a way to accomplish this?.

Thank you very much for your time and expertise.

Perseverance worths it...:)

ann
Posting Yak Master

220 Posts

Posted - 2008-01-29 : 14:58:18

You can open up the enterprise manager, select the db, then the table.
Open the table in design mode, select the column that has the default value associated with it. Look at the columns (at the bottom) and just delete your default value - I don't know how to do it via sp, but this is just as simple

chorofonfilo
Starting Member

40 Posts

Posted - 2008-01-29 : 15:20:10

quote:
Originally posted by ann

You can open up the enterprise manager, select the db, then the table.
Open the table in design mode, select the column that has the default value associated with it. Look at the columns (at the bottom) and just delete your default value - I don't know how to do it via sp, but this is just as simple

Thanks Ann, a fresh idea!, thats what i needed, now i can go to cool down my brain with some cold water and keep working afterwards.
Thank you very much.
Pd:Would be cool to know how to accomplish this ussing sp tho.

Perseverance worths it...:)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-30 : 17:15:32

ALTER TABLE <TableName> DROP CONSTRAINT <ConstraintName>

For example:

CREATE TABLE #Test 
(
	ID INT NOT NULL, 
	IsEnabled BIT NULL CONSTRAINT DF_#Test_IsEnabled DEFAULT (0),
	CONSTRAINT PK_#Test PRIMARY KEY CLUSTERED (ID) WITH FILLFACTOR = 100
)

INSERT #Test (ID) VALUES(1)

INSERT #Test
SELECT 2, 1
UNION ALL SELECT 3, 0
UNION ALL SELECT 4, 1

ALTER TABLE #Test DROP CONSTRAINT DF_#Test_IsEnabled

INSERT #Test (ID) VALUES (5)

SELECT *
FROM #Test

DROP TABLE #Test

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources