Problem: problems caused by Default

Problem:

Create table tablename (columnname VARCHAR (20) not NULL Default 0)
GO
Alter table doc drop column columnname
GO
This error message appears.
Server: Message 5074, level 16, status 1, Row 1
The object 'df _ tablename _ columnname _ 4865BE2A 'depends on the column 'columnname '.
Server: Message 4922, level 16, status 1, Row 1
Alter table drop column columnname failed because one or more objects access this COLUMN.
How can I delete such columns !!!

 

Cause: 

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 this field is forcibly modified, an error will occur.

 

Solution:

If you only want to delete this field, you only need to delete this constraint first. You can delete this field:

(1)

Declare @ SQL varchar (200)
Set @ SQL = 'alter table tablename drop constraint' + (select o. name from syscolumns c, sysobjects o where c. id = object_id ('tablename') and c. name = 'columnname' and c. cdefault = o. id)
Exec (@ SQL)
Alter table tablename drop column columnname
Go

(2)

Delete common SQL scripts with default constraints in SQL server
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 delete the field with the default value (assuming the field name is column1 ),
When "alter table table1 drop column column1" is executed, an error is returned:
The object 'df _ xxxxxxxxxxxxx' is dependent on column 'column1 '.
Alter table drop column column1failed because one or more objects access this column.

Therefore, when deleting this field, you must first Delete the corresponding default constraints in the system table. You can use the following script to delete the field:
-- This script drops the default constraint which is generated by the setting of default value.
DECLARE @ tablename VARCHAR (100), @ columnname VARCHAR (100), @ tab VARCHAR (100)
SET @ tablename = 'countrygroupemailandwaitaux'
SET @ columnname = 'actionofhasnovalidemail'

Declare @ defname varchar (100)
Declare @ cmd varchar (100)

Select @ defname = name
FROM sysobjects so
JOIN sysconstraints SC
ON so. id = SC. constid
WHERE object_name (so. parent_obj) = @ tablename
AND so. 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)

After deleting the corresponding default constraint, run the following command:
Alter table table1 drop column column1
To delete fields.

(1) and (2) Same

If you do not want to delete this field and want to modify the field type, you may use the following methods:

From the error message, we can see that the field "DF _ tablename _ columnname _ 4865BE2A" is a constraint name generated by the system. We can delete this constraint, the operations we want to perform later will also apply to the operations above to delete fields.

This constraint name can be found in the script that generates "SQL script" on the table, and then perform the following operations:

Alter table [dbo]. [epcUser] DROP
CONSTRAINT [DF _ tablename _ columnname _ 4865BE2A]

Alter Table [dbo]. [epcUser]
Alter Column IsPasswordChangeRequired tinyint Not Null

Alter table [dbo]. [epcUser] ADD
CONSTRAINT [DF _ tablename _ columnname _ 4865BE2A] DEFAULT (1) FOR [IsPasswordChangeRequired]

 

OK, so we can solve the problem.