Monday, August 11, 2008

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)

  • 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;


No comments:

Post a Comment