I have a column with default value bound to UW_ZeroDefault and I am trying get rid of it.

As I understand it, this default has been created by using CREATE DEFAULT then the column has been bound to this default by using sp_bindefault.

Usually to remove this default, you would use sp_unbinddefault, but since there is a bug in SQL Server 2008 (see KB article), I am getting an error:

Invalid object name ' '.

I cannot make any changes to the SQL Server installation, so I am looking for some workarround how to drop this thing.

Summary of the discusion below:


As suggested by @gbn, if you can get your database back to the SQL Server 2000, do it and use sp_unbindefault there. That should work.

But if you can't move it (that's my case)...


Make a copy of the table(s) and use CONSTRAINT to set a default instead of binding default. This includes dropping all kind of constraints, FKs, etc. and recreating them afterwards. It is a bad solution, but I couldn't find a better.

1 Answer 1


As per BOL rather than KB, use ALTER TABLE

ALTER TABLE Mytable DROP <defaultname>;

The name of the default could be user supplied or system generated.
To find it and generate the SQL needed:

    OBJECT_NAME(c.object_id), dc.name,
    'ALTER TABLE ' + QUOTENAME(OBJECT_NAME(c.object_id)) + ' DROP ' + QUOTENAME(dc.name);
    sys.columns c
    sys.default_constrainst dc ON c.columnid = dc.parent_column_id AND c.object_id = dc.parent_object_id
    c.name = 'UW_ZeroDefault';
  • UW_ZeroDefault is not a constraint. It was created using CREATE DEFAULT then bound with sp_bindefault and should be unbind by using sp_unbindefault. But that doesn't work in my case. Oct 3, 2011 at 15:15
  • @Radek Stromský: have you tried ALTER TABLE? You may have to remove it in SQL Server 2000 first.
    – gbn
    Oct 3, 2011 at 15:19
  • yeah, I've tried, but with no success. I assume that sp_unbindefault would work on SQL Server 2000, but in our current stage, we are unable to migrate database back from the SQL Server 2008 to 2000. It would be simpler for us to make a clone of the entire table and drop the one with the "cursed default value". Oct 3, 2011 at 16:14
  • @Radek Stromský: unfortunately, that is your solution then. Rules and this kind of default were archaic in SQL Server 2000.
    – gbn
    Oct 3, 2011 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.