Red Gate forums :: View topic - Feature Request: Turn off selected Extended Property Output
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Doc 2
SQL Doc 2 forum

Feature Request: Turn off selected Extended Property Output

Search in SQL Doc 2 forum
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.
Jump to:  
Author Message
justin_tighe



Joined: 15 May 2009
Posts: 1

PostPosted: Fri May 15, 2009 11:43 am    Post subject: Feature Request: Turn off selected Extended Property Output Reply with quote

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
Back to top
View user's profile Send private message
Linda Hawksworth



Joined: 25 Jun 2007
Posts: 192

PostPosted: Mon May 18, 2009 11:53 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
geographika



Joined: 06 May 2010
Posts: 1

PostPosted: Thu May 06, 2010 8:34 pm    Post subject: Reply with quote

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:


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
Back to top
View user's profile Send private message
Display posts from previous:   
This topic is locked: you cannot edit posts or make replies. All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group