Feature Request: Turn off selected Extended Property Output

Documents SQL Server 2005 and 2008 databases.

Moderators: JonathanWatts, David Atkinson, Michelle Taylor

Feature Request: Turn off selected Extended Property Output

Postby justin_tighe » Fri May 15, 2009 11:43 am

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
justin_tighe
 
Posts: 1
Joined: Fri May 15, 2009 11:37 am

Postby Linda Hawksworth » Mon May 18, 2009 11:53 am

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
 
Posts: 192
Joined: Mon Jun 25, 2007 11:32 am

Postby geographika » Thu May 06, 2010 8:34 pm

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.

Code: Select all

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
 
Posts: 1
Joined: Thu May 06, 2010 8:26 pm


Return to SQL Doc 2

Who is online

Users browsing this forum: No registered users and 0 guests