21 December, 2009
0 Comments
1 category
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 go 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 exec(@cmd) -- 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;
Tags: T-SQL
Category: Uncategorized