Feature Request: Turn off selected Extended Property Output

• 
  Thanks for your forum post. This feature has been requested by other customers and is logged in our tracking system. It will be considered by the SQL Doc team for a future release although we have no timescales for this at present.

  Linda Hawksworth May 18, 2009 06:53

  - 0 +
• 
  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

  geographika May 06, 2010 15:34

  - 0 +

Add comment

Please sign in to leave a comment.