Remove all MS_Diagram extended properties

When you create a view in SSMS using the wizard, it retains information for the layout designer. There's no need for this, especially as it makes my database comparisons messy.

I had already run through the following articles the first time to nuke all those metadata items. Then they restored over my dev environment and I had not saved my scripts.

• http://sqlblog.com/blogs/jamie_thomson/archive/2012/03/25/generate-drop-statements-for-all-extended-properties.aspx
• http://www.sqlservercentral.com/articles/Metadata/72609/
• http://blog.hongens.nl/2010/02/25/drop-all-extended-properties-in-a-mssql-database/
• http://msdn.microsoft.com/en-us/library/ms178595.aspx

My approach is a wee different. I'm going to use a cursor to enumerate through my results and then use sp_executesql instead of doing the string building the other fine authors were using.

This script will remove all the MS named objects attached to views. I hope that you can easily adapt this to stripping the extended properties from any object by adjusting the join to other system objects and/or using level2 specifications

DECLARE 
    @Query nvarchar(4000) = 'EXECUTE sys.sp_dropextendedproperty @name, @level0type, @level0name, @level1type, @level1name, @level2type, @level2name;'
,   @ParamList nvarchar(4000) = N'@name sysname, @level0type varchar(128), @level0name sysname, @level1type varchar(128), @level1name sysname, @level2type varchar(128), @level2name sysname'
,   @SchemaName sysname
,   @ObjectName sysname
,   @PropertyName sysname
,   @ObjectType varchar(128);

DECLARE CSR CURSOR
READ_ONLY
FOR 
SELECT
    S.name AS SchemaName
,   V.name AS ObjectName
,   EP.name AS PropertyName
,   O.type_desc AS ObjectType
FROM 
    sys.extended_properties AS EP
    INNER JOIN 
        sys.views V
        ON V.object_id = EP.major_id 
    INNER JOIN 
        sys.schemas S
        ON S.schema_id = V.schema_id 
    INNER JOIN
        sys.objects AS O
        ON O.object_id = V.object_id
WHERE 
    EP.minor_id = 0
    -- The underscore is a single character wild card, need to escape it
    AND EP.name LIKE 'MS_Dia%';


OPEN CSR;

FETCH NEXT 
FROM CSR INTO
    @SchemaName 
,   @ObjectName 
,   @PropertyName 
,   @ObjectType;
WHILE (@@fetch_status = 0)
BEGIN

    EXECUTE sys.sp_executesql 
        @Query
    ,   @ParamList
    ,   @name=@PropertyName
    ,   @level0Type = 'SCHEMA'
    ,   @level0Name = @SchemaName
    ,   @level1Type = @ObjectType
    ,   @level1Name = @ObjectName
    ,   @level2type = NULL
    ,   @level2Name = NULL;


    FETCH NEXT 
    FROM CSR INTO
        @SchemaName 
    ,   @ObjectName 
    ,   @PropertyName 
    ,   @ObjectType;
END

CLOSE CSR;
DEALLOCATE CSR;