If you want to change the value of default on a column, you must use the ALTER TABLE syntax (DROP DEFAULT and CREATE DEFAULT should be avoided, will be removed in future releases):

-- Create a default on column 'cust_name' in table 'MyCustomer' with value 'new customer'
IF OBJECT_ID('dbo.MyCustomers') IS NOT NULL
  DROP TABLE dbo.MyCustomers
CREATE TABLE dbo.MyCustomers(
  cust_id int NOT NULL,
  cust_name varchar(30) NOT NULL DEFAULT ('new customer'))

-- Remove the default from column 'MyColumn1' in table 'MyTable'
declare @defaultName varchar(100), @cmd varchar(1000)
set @defaultName =
 select name
 from sys.objects so JOIN sys.sysconstraints sc on so.object_id = sc.constid
 where object_name(so.parent_object_id) = 'MyTable'
 and so.type = 'D'
 and sc.colid = (select column_id from sys.columns where name = 'MyColumn1' and Object_ID = Object_ID(N'MyTable'))
set @cmd = 'alter table MyTable drop constraint ' + @defaultName

-- Add a default on column 'cust_name' in table 'MyCustomer' with value 'new text customer'
alter table MyCustomers add constraint DF_MyCustomers_cust_name default 'new text customer' for cust_name;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.