Database Options : Extended Properties in Documentation

To show the extended properties in the documentation ApexSQL Doc queries those from the server.

However you may not want to output all your properties in the Description section of the objects. To handle this situation ApexSQL Doc offers you to specify a common name of the properties that should be shown in this section. If you decide to do so, only extended properties matching this name will be shown. This option is called Main Extended Property Name. However this option doesn't affect the Extended Properties section of the documentation that always shows all extended properties of the object.

For information about the available buttons in the image above click here.

As we have already mentioned MS_Description is the default name specified by SQL Server when a user adds an extended property via Enterprise Managers' Table designer or via Management Studio. ApexSQL Doc therefore also sets the Main Extended Property Name value to MS_Description by default as well.

Using the following example we now demonstrate how the Main Extended Property Name value affects final output:

CREATE TABLE [dbo].[table_ext_props] (
  [first_col]  [INT]  NOT NULL,
  [second_col] [VARCHAR](50)  NULL)

GO

EXEC sys.sp_addextendedproperty  @name = N'MS_Description' ,
                                 @value = N'Description for First_col' ,
                                 @level0type = N'SCHEMA' ,
                                 @level0name = N'dbo' ,
                                 @level1type = N'TABLE' ,
                                 @level1name = N'Table_Ext_Props' ,
                                 @level2type = N'COLUMN' ,
                                 @level2name = N'First_col' 1)

GO

EXEC sys.sp_addextendedproperty  @name = N'MS_Description' ,
                                 @value = N'Description for Second_col' ,
                                 @level0type = N'SCHEMA' ,
                                 @level0name = N'dbo' ,
                                 @level1type = N'TABLE' ,
                                 @level1name = N'Table_Ext_Props' ,
                                 @level2type = N'COLUMN' ,
                                 @level2name = N'Second_col' 2)

GO

In this example we emulated a situation where a user has added two extended properties via SQL Server Management Studio:

1) "Description for First_col" to first column

2) "Description for Second_col" to second column

Here is how it looks like in the documentation:

Now let's change the name of the first property to another, for example 'Caption':

EXEC sys.sp_updateextendedproperty  @name = N'Caption' ,
                                    @value = N'Description for First_col' ,
                                    @level0type = N'SCHEMA' ,
                                    @level0name = N'dbo' ,
                                    @level1type = N'TABLE' ,
                                    @level1name = N'Table_Ext_Props' ,
                                    @level2type = N'COLUMN' ,
                                    @level2name = N'First_col'

The second property name remains "MS_Description". Our Main Extended Property Name value also remains "MS_Description".

Now let's see how this change will affect the documentation:

You see that the first property value disappeared from the Description column, because the property name was changed.

Now if we change the value of the Main Extended Property Name to 'Caption' this will affect documentation in the following manner:

The second property disappears because its name remains "MS_Description".

Copyright © 2008 ApexSQL LLC. All Rights Reserved.