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.