[RESOLVED] Removing the Deafult Value from a SQL Server 2005 Column

[jmcilhinney]

No-one here at work has ever had to perform this specific task before so no-one can tell me how they did it. I've read the documentation for the ALTER TABLE statement in Books Online and the syntax starts like this:

ALTER TABLE table_name
{ [ ALTER COLUMN column_name 
   {DROP DEFAULT 
   | SET DEFAULT constant_expression 
   | IDENTITY [ ( seed , increment ) ]
   }

Seems simple enough but when I try to execute this:

ALTER TABLE TruckRun
    ALTER COLUMN WinUserName DROP DEFAULT

in Management Studio I get this:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.

I must be missing something elementary but that looks like it should work to me.

[jmcilhinney]

Hehe. It would help if I looked at the T-SQL reference rather than the CE reference. In my defense I did filter by SQL Server 2005 rather than SQL Server 2005 Compact Edition, but the index seems to include both in that case, with CE being the first.

Anyway, even having looked at the correct documentation I still can't see how to do it. I originally added the column like this:

ALTER TABLE TruckRun
    ADD WinUserName varchar(100) NOT NULL DEFAULT ''

the default value was required because I'm adding a NOT NULL column to a table that already contains data. I then populate the column with data like so:

UPDATE TruckRun
SET WinUserName =
(
    SELECT WinUserName
    FROM User
    WHERE UserID = TruckRun.UserID
)

and now I want to remove the default value. I tried this:

ALTER TABLE TruckRun
    ALTER COLUMN WinUserName varchar(100) NOT NULL

which runs OK but doesn't remove the default. I also tried this:

ALTER TABLE TruckRun
    ALTER COLUMN WinUserName varchar(100) NOT NULL DEFAULT NULL

which, not unexpectedly, gives me this:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.

I'll keep looking but if anyone has an idea in the mean time it would be appreciated.

[GaryMazzone]

Did you try Alter Table Drop Constraint (the constraint being the Default)?

ALTER TABLE TruckRun 
     Drop Constraint [i]Constraint Name Goes Here[/i]

I found this link also

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104804

[jmcilhinney]

Did you try Alter Table Drop Constraint (the constraint being the Default)?

ALTER TABLE TruckRun 
     Drop Constraint [i]Constraint Name Goes Here[/i]

I found this link also

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104804

I didn't but I will. I'm at home at the moment but I'll give it a try when I get to work in the morning. Thanks for the input.

[jmcilhinney]

Thanks Gary. That was exactly what I needed. Getting the constraint name was a little bit of fun and games. Here's what I ended up with:

DECLARE @ParentObjectID int,
        @ParentColumnID int,
        @ConstraintName nvarchar(100),
        @SqlCmd nvarchar(100)
 
SET @ParentObjectID = OBJECT_ID(N'TruckRun')
 
SELECT @ParentColumnID = column_id
FROM sys.columns
WHERE object_id = @ParentObjectID
    AND name = N'WinUserName'
 
SELECT @ConstraintName = name
FROM sys.default_constraints
WHERE parent_object_id = @ParentObjectID
    AND parent_column_id = @ParentColumnID
 
-- The constraint name must be specified literally,
-- but it can only be determined by querying the sys.default_constraints view.
SET @SqlCmd = N'ALTER TABLE TruckRun DROP CONSTRAINT ' + @ConstraintName
EXEC sp_executesql @SqlCmd

[GaryMazzone]

Glad to finally be able to help you out.

[jmcilhinney]

I had forgotten that this application was going to deployed against SQL Server 2000 so, for completeness, here's the SQL code that works on that version:

DECLARE @TableID int,
        @ColumnID int,
        @ConstraintID int,
        @ConstraintName nvarchar(100),
        @SqlCmd nvarchar(100)
 
SET @TableID = OBJECT_ID(N'TruckRun')
 
SELECT @ColumnID = colid
FROM syscolumns
WHERE   id = @TableID
    AND name = N'WinUserName'
 
SELECT @ConstraintID = constid
FROM sysconstraints
WHERE   id = @TableID
    AND colid = @ColumnID
 
SELECT @ConstraintName = name
FROM sysobjects
WHERE id = @ConstraintID
 
-- The constraint name must be specified literally,
-- but it can only be determined by querying the sysobjects table.
SET @SqlCmd = N'ALTER TABLE TruckRun DROP CONSTRAINT ' + @ConstraintName
EXEC sp_executesql @SqlCmd