====== Managing extended properties ======

{{tag> blog tsql extended_properties}}

[[https://www.mssqltips.com/sqlservertip/5384/working-with-sql-server-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. 

<WRAP center round info 80%>
Extended properties can be attached to many kinds of database object; the scripts here relate exclusively to properties attached to schema-scoped objects (represented in ''sys.objects'') and to columns (represented in ''sys.columns'').
</WRAP>

===== Inspecting properties =====
Property values can be inspected using the DMV ''[[https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/extended-properties-catalog-views-sys-extended-properties|sys.extended_properties]]''. 

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

<code tsql>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'
</code>

===== Modifying properties and their values =====
Extended properties are created (with values) using the system SP ''[[https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addextendedproperty-transact-sql|sp_addextendedproperty]]'' and removed by ''[[https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-dropextendedproperty-transact-sql|sp_dropextendedproperty]]''. Existing property values are modified using ''[[https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-updateextendedproperty-transact-sql|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.

<code tsql>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
</code>
==== Examples ====
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.

<code tsql>-- 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
</code>
The order of the SP's parameters means you can do this more concisely/lazily:
<code tsql>
EXEC #setExtendedProperty 'dbo.MyTable', 'MyNewProperty', 'A'  
EXEC #setExtendedProperty 'dbo.MyTable', 'MyNewProperty', 'B'
EXEC #setExtendedProperty 'dbo.MyTable', 'MyNewProperty', @remove = 1
</code> 

==== Batch modification ====
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.

<code tsql>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
</code>