How to drop extended properties of all columns in a table

When synchronizing
database schema
between DBs, you may
also need to synchronize the
 extended properties.


Dropping extended properties of all columns in
a table one by one is a lot of work.

Therefore you can use the script below that will create
drop statements of extended properties for all columns
in defined table.All you have to do is to execute
the result set you got from this script.

SET NOCOUNT ONSELECT 'EXEC sp_dropextendedproperty
              @name = ''' + Extp.name + '''
             ,@level0type = ''schema''
             ,@level0name = ' + OBJECT_SCHEMA_NAME(Extp.major_id) + '
             ,@level1type = ''table''
             ,@level1name = ''' + OBJECT_NAME(Extp.major_id) + '''
             ,@level2type = ''column''
             ,@level2name = ''' + Col.name + '''
             GO'
FROM sys.columns AS Col
            LEFT JOIN sys.extended_properties AS Extp ON Col.object_id = Extp.major_id
                                                                    AND Col.column_id = Extp.minor_id
WHERE Extp.class_desc = 'OBJECT_OR_COLUMN'
               AND OBJECT_NAME(Col.object_id)='YourTableName'

Before execute this script in SSMS,
make sure you switched to 'Results to Text'

Note: In case you copied  the script above and
pasted it in SSMS and the results are cut, change the
following to 1024, for example.

Thanks for reading.