Managing extended properties

Extended properties are a means of attaching user-defined key-value pairs to database objects. This page provides some utility scripts to facilitate interaction with extended properties.

Property values can be inspected using the DMV sys.extended_properties.

The following script filters the view to schema-scoped objects and columns, and adds object and column names for readability.

SELECT 
  QUOTENAME(OBJECT_SCHEMA_NAME(xp.major_id)) + '.' + QUOTENAME(OBJECT_NAME(xp.major_id)) AS ObjectName
, c.[name] AS ColumnName
, xp.[name] AS PropertyName
, xp.[value] AS PropertyValue
FROM sys.extended_properties xp
  LEFT JOIN sys.columns c 
    ON c.[object_id] = xp.major_id
    AND c.column_id = xp.minor_id
WHERE xp.class_desc = 'OBJECT_OR_COLUMN'

Extended properties are created (with values) using the system SP sp_addextendedproperty and removed by sp_dropextendedproperty. Existing property values are modified using sp_updateextendedproperty.

The syntax for these SPs is somewhat verbose, and requires you to know whether or not a property already exists:

• To set a property's value if it exists, call sp_updateextendedproperty otherwise call sp_addextendedproperty;
• To remove an existing property call sp_dropextendedproperty. Calling the SP on a non-existent property raises an error.

The following script creates a (temporary) SP which takes a multi-part object/column name and property details, with an optional @remove flag:

• With @remove = 0 (default), the SP calls sp_updateextendedproperty or sp_addextendedproperty as appropriate;
• With @remove = 1, the SP calls sp_dropextendedproperty, but only if the property exists.

CREATE PROCEDURE #setExtendedProperty (
  @objectOrColumn NVARCHAR(500)  -- two- or three-part name e.g. '[dbo].[Table].[ColumnName]'
, @xpName SYSNAME                -- property name
, @newValue SQL_VARIANT = NULL   -- property value
, @remove BIT = 0                -- set to 1 to drop the specified property if it exists
, @execute BIT = 1               -- if 0, just print generated SQL without executing it
)
AS
 
DECLARE @schemaName NVARCHAR(128) = PARSENAME(@objectOrColumn, 3)
DECLARE @objectName NVARCHAR(128) = PARSENAME(@objectOrColumn, IIF(@schemaName IS NULL, 1, 2))
DECLARE @columnName NVARCHAR(128) = PARSENAME(@objectOrColumn, IIF(@schemaName IS NULL, 3, 1))
IF @schemaName IS NULL SET @schemaName = PARSENAME(@objectOrColumn, 2)
 
DECLARE @sql NVARCHAR(MAX)
 
SELECT 
  @sql = 'EXEC sp_' 
    + CASE @remove
        WHEN 0 THEN IIF(xp.major_id IS NULL, 'add', 'update')
        ELSE IIF(xp.major_id IS NULL, NULL, 'drop')
      END
    + 'extendedproperty
  @name = ''' + REPLACE(@xpName, '''', '''''') + '''
, @level0name = ''' + REPLACE(OBJECT_SCHEMA_NAME(o.[object_id]), '''', '''''') 
    + ''', @level1name = ''' + REPLACE(OBJECT_NAME(o.[object_id]), '''', '''''') 
    + '''' + IIF(c.[name] IS NOT NULL, ', @level2name = ''' + REPLACE(c.[name], '''', '''''') + '''', '') + '
, @level0type = ''SCHEMA'', @level1type = ''TABLE''' 
    + IIF(c.[name] IS NOT NULL, ', @level2type = ''COLUMN''', '')
    + IIF(@remove = 0, '
, @value = @newValue', '')
FROM sys.objects o
  LEFT JOIN sys.columns c 
    ON c.[object_id] = o.[object_id]
    AND c.[name] = @columnName
  LEFT JOIN sys.extended_properties xp
    ON xp.major_id = o.[object_id]
    AND xp.minor_id = COALESCE(c.column_id, 0)
    AND xp.[name] = @xpName
    AND xp.class_desc = 'OBJECT_OR_COLUMN'
WHERE OBJECT_SCHEMA_NAME(o.[object_id]) = @schemaName
AND OBJECT_NAME(o.[object_id]) = @objectName
 
PRINT @sql
 
IF @execute = 1
BEGIN
 
  IF @remove = 1
    EXEC sp_executesql @stmt = @sql
  ELSE
    EXEC sp_executesql 
      @stmt = @sql
    , @params = N'@newValue SQL_VARIANT'
    , @newValue = @newValue
 
END

Assuming the property 'MyNewProperty' does not exist on table 'dbo.MyTable', the set of SP calls in the script below will:

• create the property with the value 'A';
• update its value to 'B';
• drop the property.

-- create the property with value 'A'
EXEC #setExtendedProperty
  @objectOrColumn = 'dbo.MyTable'
, @xpName = 'MyNewProperty'
, @newValue = 'A' 
 
 -- update the property's value to 'B'
EXEC #setExtendedProperty
  @objectOrColumn = 'dbo.MyTable'
, @xpName = 'MyNewProperty'
, @newValue = 'B' 
 
-- drop the property
EXEC #setExtendedProperty
  @objectOrColumn = 'dbo.MyTable'
, @xpName = 'MyNewProperty'
, @remove = 1
 
-- this does nothing (but throws no errors)
EXEC #setExtendedProperty
  @objectOrColumn = 'dbo.MyTable'
, @xpName = 'MyNewProperty'
, @remove = 1

The order of the SP's parameters means you can do this more concisely/lazily:

EXEC #setExtendedProperty 'dbo.MyTable', 'MyNewProperty', 'A'  
EXEC #setExtendedProperty 'dbo.MyTable', 'MyNewProperty', 'B'
EXEC #setExtendedProperty 'dbo.MyTable', 'MyNewProperty', @remove = 1

The script below makes no changes, but builds and returns code for two SP calls based on existing extended properties:

• a call to sp_updateextendedproperty which sets the property to its current value;
• a call to sp_dropextendedproperty which drops the property.

Modify the WHERE clause to return only the properties you're interested in, then copy the call(s) you need out of the results pane and into an SSMS query editor window. This provides a quick way of building scripts to modify large numbers of properties.

SELECT 
  QUOTENAME(OBJECT_SCHEMA_NAME(xp.major_id)) + '.' + QUOTENAME(OBJECT_NAME(xp.major_id))
    + IIF(c.[name] IS NOT NULL, '.' + QUOTENAME(c.[name]), '') AS ObjectOrColumn
, xp.[name] AS ExtendedPropertyName
, 'EXEC sp_updateextendedproperty ' + expr.ParameterList + '
, @value = ' + COALESCE('''' + REPLACE(CAST(xp.[value] AS NVARCHAR(MAX)), '''', '''''') + '''', 'NULL') AS UpdateSpCall
, 'EXEC sp_dropextendedproperty ' + expr.ParameterList AS DropSpCall
FROM sys.extended_properties xp
  LEFT JOIN sys.columns c 
    ON c.[object_id] = xp.major_id
    AND c.column_id = xp.minor_id
  OUTER APPLY (
    SELECT 
      '@name = ''' + REPLACE(xp.[name], '''', '''''') + '''
, @level0name = ''' + REPLACE(OBJECT_SCHEMA_NAME(xp.major_id), '''', '''''') 
    + ''', @level1name = ''' + REPLACE(OBJECT_NAME(xp.major_id), '''', '''''') 
    + '''' + IIF(c.[name] IS NOT NULL, ', @level2name = ''' + REPLACE(c.[name], '''', '''''') + '''', '') + '
, @level0type = ''SCHEMA'', @level1type = ''TABLE''' 
    + IIF(c.[name] IS NOT NULL, ', @level2type = ''COLUMN''', '') AS ParameterList
   ) expr
WHERE xp.class_desc = 'OBJECT_OR_COLUMN'
--AND xp.[name] = 'MyNewProperty'  -- for example