Feature Request: Turn off selected Extended Property Output

justin_tighe

The extended properties for a database object can include a lot of noise such as the MS_DiagramPane1 extended property.

On a future release can you include an option to turn off selected extended properties from appearing in the documentation in the SQL code and the Extended Properties section of the documentation. This can help to keep the size of the documentation down

Linda Hawksworth

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.

geographika

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