Using SQL Compare to update MS_Description extended property

mlukas

We have been using SQL Doc to document our databases. This involves adding text to the MS_Description extended properties.

All of our stored procedures, functions and views have a structured text header block that we use for documentation. We have another stored procedure that extracts text from these header blocks and updates the extended properties. Then we use SQL Doc to generate the documentation.

Extended properties have a limit of 7500 bytes. Since our header blocks are in English, when we update the extended properties, we treat the text as varchar instead of nvarchar. This effectively gives us more characters for the documentation.

When we run SQL Compare against two databases, it identifies differences in the extended properties, but when it goes to synchronize the databases, it seems to treat the text in the extended properties as nvarchar. In some cases, the resulting text then exceeds the 7500 byte maximum and the synchronization fails.

Is there a way to force SQL Compare to update the extended properties as varchar instead of nvarchar?

Chris Auckland

Thanks for your post.

I'm afraid there isn't a way to set SQL Compare to set extended properties with varchar instead of nvarchar. It sounds like a little bit of a hack anyway.

You could manually edit the sync script that SQL compare generates and remove the N' from the name parameter. i.e.

Output the sync script and then do a find and replace on

sys.sp_addextendedproperty @name=N'
to
sys.sp_addextendedproperty @name='

I hope this is helpful