Delete the default value of a column in the SQL Server database table

In SQL Server, if the default value is set for a field in the table, a default constraint is generated in the system table sysobjects.
If you want to perform operations on the field with the default value, such as changing the column type and deleting the column, an error is reported. Therefore, before performing operations on this field, you can use the following script to delete the default value:

Declare @ tablename varchar (100), @ columnname varchar (100), @ tab varchar (100) set @ tablename = 'table name' set @ columnname = 'field name' declare @ defname varchar (100) Declare @ cmd varchar (100) select @ defname = Name from sysobjects a join sysconstraints SC on. id = SC. constid where object_name (. parent_obj) = @ tablename and. xtype = 'D' and SC. colid = (select colid from syscolumns where id = object_id (@ tablename) and name = @ columnname) select @ cmd = 'alter table' + @ tablename + 'drop constraint' + @ defname if @ CMD is null print 'No Default constraint to drop' exec (@ cmd)