[SQL Server] SQL statements Delete columns with default values in a table.

If the default value of the column is set when a column is added dynamically, an error is reported when the alter table tablename drop column columnname statement is used. For example,

"Message 5074, level 16, status 1, 1st rows
The object 'df _ tb_salarypar _ AA _ 7db89c09 'depends on the column 'A '.
Message 4922, level 16, status 9, 1st rows
Alter table drop column AA failed because one or more objects access this column ."

The default constraint exists.

What we need to do now:

1. Find the constraint name for this column in the table.

Declare @ name varchar (50)
Select @ name = B. name from sysobjects B join syscolumns A on B. ID = A. cdefault
Where a. ID = object_id ('tablename ')
And a. Name = 'columname'

2. Delete the constraint and delete the column.

Exec ('alter table tablename drop constraint' + @ name)
Exec ('alter table tablenamedrop column columname ')