23 August 2013

SQL server : Delete field description in order to allow a DB to be exported as a BACPAC file

Run the following query :
--tables
select 'EXEC sp_dropextendedproperty
@name = ''MS_Description''
,@level0type = ''schema''
,@level0name = ' + object_schema_name(extended_properties.major_id) + '
,@level1type = ''table''
,@level1name = ' + object_name(extended_properties.major_id)
from sys.extended_properties
where extended_properties.class_desc = 'OBJECT_OR_COLUMN'
and extended_properties.minor_id = 0
and extended_properties.name = 'MS_Description'
--columns
select 'EXEC sp_dropextendedproperty
@name = ''MS_Description''
,@level0type = ''schema''
,@level0name = ' + object_schema_name(extended_properties.major_id) + '
,@level1type = ''table''
,@level1name = ' + object_name(extended_properties.major_id) + '
,@level2type = ''column''
,@level2name = ' + columns.name
from sys.extended_properties
join sys.columns
on columns.object_id = extended_properties.major_id
and columns.column_id = extended_properties.minor_id
where extended_properties.class_desc = 'OBJECT_OR_COLUMN'
and extended_properties.minor_id > 0
and extended_properties.name = 'MS_Description'
And copy-paste-run the result as a new Query.

#1
Post a Comment