MSSQL: updating and dropping extended properties (column comments)

To change a column comment in MSSQL, use sp_updateextendedproperty

When the user tries to change the column comment to an empty string, use sp_dropextendedproperty instead of trying to set the comment to a blank string.

Let me say, managing column and table comments in MSSQL is a mess.

But yes...

This is still a problem.

I'm just trying to change a column from nullable to nullable. I'm not touching the comments. There aren't even comments.

But then this:

ALTER TABLE "dbo"."Bond"
    ALTER COLUMN  "Apply28FebEndOfMonthException" BIT NOT NULL;
EXECUTE sp_addextendedproperty 'MS_Description', '', 'Schema', 'dbo', 'table', 'Bond', 'column', 'Apply28FebEndOfMonthException';
/* SQL Error (15233): Property cannot be added. Property 'MS_Description' already exists for 'dbo.Bond.Apply28FebEndOfMonthException'. */

Solution: Don't add the extended property if the trimmed string is an empty string.

Also happens when ADDING a column. If the comment is empty, don't set the extended property.

ALTER TABLE "dbo"."YieldCurve" ADD "IncludeInAutomatedGeneration" BIT NULL;
EXECUTE sp_addextendedproperty 'MS_Description', '', 'Schema', 'dbo', 'table', 'YieldCurve', 'column', 'IncludeInAutomatedGeneration';