SQL Server: alternative to “ALTER TABLE TableName ALTER COLUMN ColumnName DROP DEFAULT”
Posted by jpluimers on 2010/09/15
SQL Server used to support the “ALTER TABLE TableName ALTER COLUMN ColumnName DROP DEFAULT”:
- SQL Server Compact 3.5 Service Pack 1 Books Online
- SQL Server CE Books Online
- SQL Server 2005 Row Versioning-Based Transaction Isolation
In the current Books Online version for the SQL Server 2005 version of ALTER TABLE documentation, the “DROP DEFAULT” has been disappeared.
In the SQL Server 2005 documentation, Microsoft already indicated that the “DROP DEFALT” would be removed in a future version.
I was surprised it already had disappeared in SQL Server 2005 Service Pack 3, especially since it is documented to be removed after SQL Server 2008 R2.
By removing this feature, SQL Server is moving further away from the SQL-92 standard.
Anyway: When you try to perform a ALTER TABLE TableName ALTER COLUMN ColumnName DROP DEFAULT, now you get this error message:
Incorrect syntax near the keyword ‘DEFAULT’.
Below is how I approached towards a solution.The Microsoft documentation suggest you use sp_unbinddefault like this:EXEC sp_unbindefault ‘TableName.ColumnName’
But that also results in an error message:
Cannot unbind from ‘TableName.ColumnName’. Use ALTER TABLE DROP CONSTRAINT.
The hint is in trying to drop the column; then you get an error message likt this:
The object ‘DF__TableName__Colum__6D4D2A16’ is dependent on column ‘ColumnName’.
So the trick is to find the name of the DEFAULT constraint, so you can perform a proper DROP CONSTRAINT on that.
Luckily, Rob Fairly explains how to get the name of the DEFAULT constraint.
Then it is just a matter of calling sp_executesql to execute the DROP CONSTRAINT with the right name.
On vbforums, John McIlhinney explains how to call sp_executesql with a dynamically constructed SQL statement.
This is the resulting code:
-- modified from http://msmvps.com/blogs/robfarley/archive/2007/11/26/two-ways-to-find-drop-a-default-constraint-without-knowing-its-name.aspx
declare @TableName nvarchar(256)
declare @ColumnName nvarchar(256)
set @TableName = N'TableName'
set @ColumnName = N'ColumnName'
declare @ConstraintName nvarchar(256)
select @ConstraintName = d.name
from sys.tables t
join
sys.default_constraints d
on d.parent_object_id = t.object_id
join
sys.columns c
on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where t.name = @TableName
and c.name = @ColumnName
-- modified from http://www.vbforums.com/archive/index.php/t-535331.html
declare @SqlCmd nvarchar(256)
-- The constraint name must be specified literally,
-- but it can only be determined by querying the sysobjects table.
SET @SqlCmd = N'ALTER TABLE ' + @TableName + N' DROP CONSTRAINT ' + @ConstraintName
EXEC sp_executesql @SqlCmd
;
Alter TABLE ClientAudition
drop Column ClAud_Year
;
All this trickery would not have been needed if the original DDL would be like this (borrowed from Microsoft’s Migrating from MySQL to SQL Server 2005 guide):
MySQL Example: create table alter_def (i int not null); insert alter_def values (); alter table alter_def alter i set default 99; insert alter_def values (); alter table alter_def alter i drop default; insert alter_def values (); select * from alter_def; -- 0 99 0 Solution: Convert ALTER...SET DEFAULT and ALTER...DROP DEFAULT clauses to ADD/DROP DEFAULT constraint clauses. SQL Server Example: create table alter_def (i int not null); insert alter_def default values; -- Cannot insert the value NULL into 'i' alter table alter_def add constraint i_def default 99 for i; insert alter_def default values; alter table alter_def drop constraint i_def; insert alter_def default values; -- Cannot insert the value NULL into 'i' select * from alter_def; -- 99
Old databases often are not always are structured the way you would want to, so hopefully the solution presented above will help a few of you out.
–jeroen
The Wiert Corner - irregular stream of stuff 
Leave a comment