Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

SOLUTION 1:

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)...

SOLUTION 2:

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.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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:

SELECT
    OBJECT_NAME(c.object_id), dc.name,
    'ALTER TABLE ' + QUOTENAME(OBJECT_NAME(c.object_id)) + ' DROP ' + QUOTENAME(dc.name);
FROM
    sys.columns c
    JOIN
    sys.default_constrainst dc ON c.columnid = dc.parent_column_id AND c.object_id = dc.parent_object_id
WHERE
    c.name = 'UW_ZeroDefault';
share|improve this answer
    
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. –  Radek Stromský Oct 3 '11 at 15:15
    
@Radek Stromský: have you tried ALTER TABLE? You may have to remove it in SQL Server 2000 first. –  gbn Oct 3 '11 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". –  Radek Stromský Oct 3 '11 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 '11 at 16:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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