How to Drop a Column

How to Drop a Column

• Discover whether there is a constraint;

DECLARE @ConstraintName NVARCHAR(50);
SELECT @ConstraintName = [name]

FROM sys.objects o

WHERE o.[parent_object_id]=OBJECT_ID('Schema.TableName')

AND o.type='D'

AND o.[name] LIKE '%First5LettersOfColumnName%'

• If there is, remove it;

IF NOT (@ConstraintName IS NULL)

   EXECUTE ('ALTER TABLE Schema.TableName 
   DROP CONSTRAINT ' + @ConstraintName)
GO

• Remove the column

IF EXISTS(SELECT * FROM sys.columns WHERE [name]='ColumnName 
   AND [object_id]=OBJECT_ID('Schema.TableName'))

      ALTER TABLE Schema.TableName

      DROP COLUMN ColumnName;

GO