Tips and Tricks for:  SQL Server 2000

The Problem:   How to create a SQLPROPERTY value over 255 character

When using SQL Query Analyzer's user interface, extended properties, such as descriptions may only be 256 character.  Recently I was asked how to create a longer property so that documentation for a stored procedure could be saved in sysproperties.

Solution: sp_addextendedproperty allows the addition of large properties.

The user interface of SQL Query Analyzer may have a 256 character limitation but if you examine the sysproperties table, which is in every database, you'll find that the value column is a sql_variant.  sql_variant can hold any SQL type that can fit into the row.  There are actually three stored procedures for managing extended properties:

• sp_addextededproperty
• sp_updateextendedproperty
• sp_dropextendedproperty

Here's a script that shows how to add a long description for a stored procedure:

DECLARE @RC int
DECLARE @name nvarchar(128)
DECLARE @value sql_variant
DECLARE @level0type varchar(128)
DECLARE @level0name nvarchar(128)
DECLARE @level1type varchar(128)
DECLARE @level1name nvarchar(128)
DECLARE @level2type varchar(128)
DECLARE @level2name nvarchar(128)
-- Set parameter values
select @name = 'DESCRIPTION'
     , @Value = REPLICATE('0123456789', 30)
     , @level0type = 'USER'
     , @level0name = 'dbo'
     , @level1type = 'PROCEDURE'
     , @level1name = 'usp_AppSession_Create'
EXEC @RC = sp_addextendedproperty @name, @value
                          , @level0type, @level0name
                          , @level1type, @level1name
                          , @level2type, @level2name

 

Once the extended property has been added to the database, you'll have to use the fn_listextendedproperty system user-defined function.  See Chapter 15 of Transact-SQL User-Defined Fucntions for a complete explanation of this UDF.