About a year ago, we tried using a tool to get our SQL schema into a souce control system. It was supposed to work like this: every night the process would run and checkin each object as a file into TFS.
Well, aside from not working, it also added Extended Properties to all our objects in SQL Server.
Not really a big deal, except when we would run our database comparison tools between production and development these would cause it so that almost everything would appear out of sync.
I ended up writing my own service to check in all our SQL objects into TFS, but we were left with all these Extended Properties all over the place.
So I finally got around to cleaning them up using the script below:
select 'EXEC sp_dropextendedproperty
@name = ''' + sys.extended_properties.name + '''
,@level0type = ''schema''
,@level0name = ' + object_schema_name(extended_properties.major_id) + '
,@level1type = ''' +
CASE WHEN
xtype = 'U' THEN 'table'
WHEN
xtype = 'FN' THEN 'function'
WHEN
xtype = 'V' THEN 'view'
WHEN
xtype = 'P' THEN 'procedure' end
+ '''
,@level1name = ''' + object_name(extended_properties.major_id) + ''''
from sys.extended_properties
INNER JOIN sysobjects ON sys.extended_properties.major_id = sysobjects.id
where extended_properties.class_desc = 'OBJECT_OR_COLUMN'
and extended_properties.minor_id = 0
and extended_properties.name like 'VSS%'
This script will produce a record set that you can copy and paste into another query window to run to clean out all the extended properties.
Note: all the generated extended properties that this tool created on our DB started with “VSS” so that is why I have that extra item in my where clause. You’ll probably want to remove that.