Chris May

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.