Wednesday 29 July 2015

Column already has a DEFAULT bound to it SQL server error

When i was altering a table with default column value it was giving an error, i.e Column already has a DEFAULT bound to it. When i investigated i found there was already a default constraint was enable for the same column.

So first off all i checked how many constraint was there for my table with syntex

EXECUTE [dbo].[sp_helpconstraint] 'mytablename'
GO

And then

alter table mytablename
drop constraint DF__mytablename__theme__0A9D95DB

No there was no default constraint on same column
And i execute following syntax

ALTER TABLE [dbo].[mytablename]  add DEFAULT ('defaultval') for mycolumn

Its works for me, and hope for you to :)