I ran into the same problem - lots of noise on my View pages, and agree this should be a configurable option.
In the meantime I wrote a script to remove these extended properties from all Views in my database.
Note, if you have spent hours laying out your views then do not run this, or run it on a copy of your database.
There are a couple of extended properties that can be removed for views:
MS_DiagramPane1 MS_DiagramPaneCount
Modify the property name in the code below to choose which one to remove - it can be run multiple times. DECLARE @view VARCHAR(100),
@schema VARCHAR(100)
-- Cursor to work through our procs
DECLARE viewCursor CURSOR LOCAL FAST_FORWARD FOR SELECT p.[name] AS [view],
s.[name] AS [schema]
FROM sys.views p
INNER JOIN sys.schemas s
ON s.schema_id = p.schema_id
WHERE s.[name] = 'dbo'
order by p.[name] asc;
OPEN viewCursor;
FETCH NEXT FROM viewCursor INTO @view,
@schema;
WHILE (@@FETCH_STATUS = 0)
BEGIN
--print @schema + '.' + @view
exec sp_executesql N'if (SELECT count(*)
FROM ::fn_listextendedproperty (@propertyName, @myLevel0Type, @myLevel0Name, @myLevel1Type, @myLevel1Name, @myLevel2Type, @myLevel2Name))> 0
begin
EXEC sp_dropextendedproperty @propertyName, @myLevel0Type, @myLevel0Name, @myLevel1Type, @myLevel1Name, @myLevel2Type, @myLevel2Name;
end',
--make sure the variables below are large enough for the supplied parameters
N'@propertyName nvarchar(max),@myLevel0Type nvarchar(6),@myLevel0Name nvarchar(8),@myLevel1Type nvarchar(9),@myLevel1Name nvarchar(4000),@myLevel2Type nvarchar(4000),@myLevel2Name nvarchar(4000),@propertyValue nvarchar(4000)'
,@propertyName=N'MS_DiagramPaneCount', --MS_DiagramPane1 --MS_DiagramPaneCount
@myLevel0Type=N'SCHEMA',
@myLevel0Name=@schema,
@myLevel1Type=N'VIEW',
@myLevel1Name=@view,
@myLevel2Type=NULL,@myLevel2Name=NULL,
@propertyValue=NULL;
-- Get the next row
FETCH NEXT FROM viewCursor INTO @view,
@schema;
END
-- Clean up
CLOSE viewCursor;
DEALLOCATE viewCursor;
http://geographika.co.uk / comments
- Community
- SQL Doc Previous Versions
- Feature Request: Turn off selected Extended Property Output
I ran into the same problem - lots of noise on my View pages, and agree this should be a configurable option.
In the meantime I wrote a script to remove these extended properties from all Views in ...
0 votes
