2

Server Management Studio tends to be a bit un-intuitive when it comes to managing Extended Properties, so can anyone recommend a decent tool that improves the situation.

One thing I would like to do is to have templates that I can apply objects, thus standardising the nomenclature and content of the properties applied to objects.

Jon Lin
  • 142,182
  • 29
  • 220
  • 220
Martin
  • 39,569
  • 20
  • 99
  • 130

2 Answers2

5

Take a look at Data Dictionary Creator, an open source tool I wrote to make it easier to edit extended properties. It includes the ability to export the information in a variety of formats, as well.

http://www.codeplex.com/datadictionary

enter image description here

Pops
  • 30,199
  • 37
  • 136
  • 151
Jon Galloway
  • 52,327
  • 25
  • 125
  • 193
1

You might also think about having a nice re-runnable script that lets you maintain the extended properties. The system stored procedures for doing this work well, but they are a pain, so I wrap them with my own stored procedure so I can more easily deal with them.

For example, below is a stored procedure targeted at column level extended properties that a) checks to see if the extended property already exists, and b) if so drops it, and c) then adds it.

This lets me maintain a clean re-runnable (which is critical for automated build processes) script of simple one liners to add the extended properties (column level only - you'd need to modify this one or write a similar one for other object types).

Here is the sproc:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].                    [snap_xpColumn_addUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].snap_xpColumn_addUpdate
GO

CREATE PROCEDURE [dbo].[snap_xpColumn_addUpdate]
    @TableName NVARCHAR(255), 
    @ColumnName NVARCHAR(255),
    @ExtPropName NVARCHAR(255), 
    @ExtPropValue NVARCHAR(255), 
    @SchemaOwner NVARCHAR(255) = 'dbo'
AS

    IF EXISTS(SELECT * FROM     ::fn_listextendedproperty(@ExtPropName,'SCHEMA',@SchemaOwner,
                                                          'TABLE',@TableName,'COLUMN',@ColumnName))
    BEGIN
        -- drop it
        EXEC sys.sp_dropextendedproperty @name=@ExtPropName, 
                                             @level0type=N'SCHEMA',
                                             @level0name=@SchemaOwner, 
                                             @level1type=N'TABLE',
                                             @level1name=@TableName, 
                                             @level2type=N'COLUMN',
                                             @level2name=@ColumnName
    END

    -- add it
    EXEC sys.sp_addextendedproperty @name=@ExtPropName, 
                                        @value=@ExtPropValue, 
                                        @level0type=N'SCHEMA',
                                        @level0name=@SchemaOwner, 
                                        @level1type=N'TABLE',
                                        @level1name=@TableName, 
                                        @level2type=N'COLUMN',
                                        @level2name=@ColumnName


GO