Modify the default value of a field in mssql

1. Obtain the default values of all tables in the database:

The code is as follows:	Copy code
Select T3.name as table name, t1.name as field name, t2.text as default value, t4.name From syscolumns t1, syscomments t2, sysobjects t3, sysobjects t4 Where t1.cdefault = t2.id and t3.xtype = 'u' and t3.id = t1.id And t4.xtype = 'd' and t4.id = t2.id;

2. Generate a statement to delete all default values:

The code is as follows:	Copy code
Select "alter table" + t3.name + "drop constraint" + t4.name + ";" From syscolumns t1, syscomments t2, sysobjects t3, sysobjects t4 Where t1.cdefault = t2.id and t3.xtype = 'u' and t3.id = t1.id And t4.xtype = 'd' and t4.id = t2.id;

3. Generate statements to add all default values in batches:

The code is as follows:	Copy code
Select "alter table" + t3.name + "add default (1) for" + t1.name + ";" From syscolumns t1, syscomments t2, sysobjects t3, sysobjects t4 Where t1.cdefault = t2.id and t3.xtype = 'u' and t3.id = t1.id And t4.xtype = 'd' and t4.id = t2.id;