How to drop a column with a default value constraint in SQL Server

It’s frustrated me a few times how difficult it is to drop a column in SQL Server when that column has a default value set on it.  You’ll get an error message like this:

Msg 5074, Level 16, State 1, Line 27
The object 'DF__TableName__ColumnName__04DCFCE9' is dependent on column 'ColumnName'.
Msg 4922, Level 16, State 9, Line 27
ALTER TABLE DROP COLUMN ColumnName failed because one or more objects access this column.

If you use SQL Server Management Studio, it takes care of this for you automatically, dropping the default constraint along with the column.  However, if you need to do it programmatically, it’s harder.  The name of the default constraint might have been automatically generated and hard to predict, so you need to look up the default constraint name first, and then drop the column.

Here’s a code snippet that’ll drop a column with a default constraint:


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)
IF EXISTS (SELECT * FROM syscolumns WHERE id=object_id('__TableName__') AND name='__ColumnName__')
EXEC('ALTER TABLE __TableName__ DROP COLUMN __ColumnName__')

Just replace __TableName__ and __ColumnName__ with the appropriate values.  You can safely run this even if the column has already been dropped.

Bonus:  Here’s the code to drop foreign keys and other types of constraints.


IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '__TableName__' AND COLUMN_NAME = '__ColumnName__')
BEGIN
SELECT @ConstraintName = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '__TableName__' AND COLUMN_NAME = '__ColumnName__'
EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)
END

This entry was posted in Uncategorized. Bookmark the permalink.

10 Responses to How to drop a column with a default value constraint in SQL Server

  1. Script work properly.
    Thanks for sharing this

  2. Great script! Worked the first time. Thanks!

  3. shaimaa says:

    thanks, the script work successfully

  4. HopeFairy says:

    thank you very much. I had to drop a column with constraint for a few hundred tables. I was able to use your script to create another script that would cursor through a whole bunch of tables.

  5. Huong Nguyen Van says:

    This is exactly what I am looking for. Thank you so much!

  6. RishiAnshuPratapReddy says:

    ALTER TABLE [dbo].[Listing] ADD DEFAULT ((2)) FOR [ModiByUserID]
    GO

    Here Listing is My table Name and ModiByUserID is My Column , so i want to delete that default(2) value.Can any body tel me?

  7. RishiAnshuPratapReddy says:

    How to drop a column with a default value Without constraint in SQL Server

  8. javeed says:

    Awesome, worked fine

  9. Great one!! This is good example for drop the column in database. I’m saving this for future reference.

    Thanks.

  10. test says:

    Just for future reference, when adding fields with defaults, always name the constraint, so you can find it to delete it.

Leave a Reply to Cliff Richardson Cancel reply

Your email address will not be published. Required fields are marked *