Sql-server – An alternative for sp_unbinddefault

default valuesql-server-2008

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.

Best Answer

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';
Related Question