sp_extendedproperty

Introduction

If you have been working with Extended Properties, you would likely have been annoyed by the use of sp_addextendedproperty, sp_updateextendedproperty and sp_dropextendedproperty especially if you are updating a lot.

Using the Code

Copy and paste the code and run it, it will create a new system wide procedure (can be called from any database on the server) which has all functions built-in to figure out if you need to Add, Update or Drop the extended property.

USE [master]
GO
CREATE PROCEDURE [dbo].[sp_extendedproperty]
(
 @name SYSNAME
 ,@value SQL_VARIANT = NULL
 ,@level0type VARCHAR(128) = NULL
 ,@level0name SYSNAME = NULL
 ,@level1type VARCHAR(128) = NULL
 ,@level1name SYSNAME = NULL
 ,@level2type VARCHAR(128) = NULL
 ,@level2name SYSNAME = NULL
)
AS BEGIN
 
 IF @value IS NULL
 BEGIN
  EXEC sp_dropextendedproperty @name, @level0type, @level0name, @level1type, _
       @level1name, @level2type, @level2name
 END
 ELSE
 BEGIN
  IF EXISTS(SELECT value FROM ::fn_listextendedproperty(@name, @level0type, _
            @level0name, @level1type, @level1name, @level2type, @level2name))
   EXEC sp_updateextendedproperty @name, @value, @level0type, @level0name, _
         @level1type, @level1name, @level2type, @level2name
  ELSE
   EXEC sp_addextendedproperty @name, @value, @level0type, @level0name, _
         @level1type, @level1name, @level2type, @level2name
 END
END;
GO
EXEC sys.sp_MS_marksystemobject sp_extendedproperty --Undocumented procedure to turn the procedure 
                                                    --into a system procedure

Usage

Assuming you have a table called dbo.company, you can call the procedure like this:

EXEC [sp_extendedproperty] @name=N'MS_Description', @value=N'Contains a list of companies', _
@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', _
@level1name=N'Company' --ADDS the extended property called MS_Description to the table 
                       --with the specified value.
EXEC [sp_extendedproperty] @name=N'MS_Description', @value=N'Contains a list of companies _
         used in this database', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', _
         @level1name=N'Company' --UPDATES the extended property called MS_Description on the table 
                                --with the specified value.
EXEC [sp_extendedproperty] @name=N'MS_Description', @level0type=N'SCHEMA', @level0name=N'dbo', _
         @level1type=N'TABLE', @level1name=N'Company' --DROPS the extended property called 
                           --MS_Description on the table because the specified value is NULL.

History

• 30-12-2016: Version 1 posted