Delete table columns during storage

When dynamically creating a database table field, if the "empty" attribute of this field is not empty, and the table is associated with other tables, deleting the column dynamically may fail. The error is reported because one or more objects access this column. The solution is to first Delete the constraint on the column and then delete the column!

The stored procedure is as follows:

Create procedure pr_deltablerow
@ Tablename varchar (100), @ datafieldname varchar (100)
As

Begin tran

Declare @ sqldel varchar (500), @ df_name varchar (500)

Set @ sqldel =''
/* Determine whether the Column exists */
If exists (select 1 from syscolumns where id = object_id (@ tablename) and name = @ datafieldname)
Begin
/* Query the constraints of this column */
Select @ df_name = Name from sysobjects where xtype = 'D' and parent_obj = object_id (@ tablename) and exists (select * From syscolumns where id = object_id (@ tablename) and info = colid and name = @ datafieldname)
/* Delete the constraint of this column */
Set @ sqldel = 'alter table' + @ tablename + 'drop constraint' + @ df_name
Exec (@ sqldel)
 
/* Now this column is available */
Set @ sqldel = 'alter table' + @ tablename + 'drop column' + @ datafieldname
Exec (@ sqldel)
End

If (@ error = 0)

Commit tran

Else

Rollback tran