Drop column and all dependent objects using data definition language

Question

I need to remove a column from a table, but when I try to delete it:

The object 'object_name' depends on the object in the column 'column_name'.

ALTER TABLE DROP COLUMN column_name failed because one or more objects are a column.

I can search for the dependency in the system tables and delete it manually, but I need to perform the migration (using SQL DDL ), so all the other members of the team just do the update, start the migration and should not ruin the system objects.

Answer 1

Try this code:

Declare @TABLENAME varchar(max), @COLUMN varchar(max)
SET @TABLENAME = 'YOURTableName'
SET @COLUMN = 'YOURColumnName'
Declare @CONSTRAINT varchar(max)
                    set @CONSTRAINT ='ALTER TABLE '+@TABLENAME+' DROP CONSTRAINT '
                    set @CONSTRAINT = @CONSTRAINT + (select SYS_OBJ.name as CONSTRAINT_NAME
                    from sysobjects SYS_OBJ
                    join syscomments SYS_COM on SYS_OBJ.id = SYS_COM.id
                    join sysobjects SYS_OBJx on SYS_OBJ.parent_obj = SYS_OBJx.id 
                    join sysconstraints SYS_CON on SYS_OBJ.id = SYS_CON.constid
                    join syscolumns SYS_COL on SYS_OBJx.id = SYS_COL.id
                    and SYS_CON.colid = SYS_COL.colid
                    where
                    SYS_OBJ.uid = user_id() and SYS_OBJ.xtype = 'D'
                    and SYS_OBJx.name=@TABLENAME and SYS_COL.name=@COLUMN)
                    exec(@CONSTRAINT)

alter:

ALTER TABLE YOURTABLENAME
DROP COLUMN YOURCOLUMNNAME

, .

EDIT - :

, , DEFAULT_CONSTRAINTS, :

DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID('__TableName__') AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = N'__ColumnName__' AND object_id = OBJECT_ID(N'__TableName__'))
IF @ConstraintName IS NOT NULL
    EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)