You do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
[quoted text, click to view]
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.


thanks in advance

ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>

If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:

DECLARE @constraint_name SYSNAME

-- remove all the defaults
WHILE 1=1
BEGIN
SET @constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))

IF @constraint_name IS NULL BREAK

EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @constraint_name)
END


--
Jacco Schalkwijk
SQL Server MVP


[quoted text, click to view]

What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.


thanks in advance

excellent !
thank a lot : )