UPSERT a Column Description in SQL Server

This is Part 3 in a series of articles discussing field comments on linked tables.

In part 2, I wrote about two built-in stored procedures you can use to set column descriptions to SQL Server tables.  You need one procedure to add a column description and a different one to edit an existing description.  

If you use the SSMS GUI, those details are abstracted away for you.  The problem with using the GUI, though, is that it is inefficient.  That's especially true if you are setting descriptions for many columns.  What we really need is a way to combine those two operations into one.

VBA to Generate T-SQL for a Field Description "UPSERT"

UPSERT is a portmanteau of the words UPDATE and INSERT.  The idea is to create T-SQL code that handles both of the above scenarios: if the field description is missing, we will add it; if the field description exists, we will replace it.

Here's the VBA function that does that:

'---------------------------------------------------------------------------------------
' Procedure : UpdateColDescDDL
' Author    : Mike Wolfe
' Date      : 9/8/2014 - 5/27/2021
' Purpose   : Generates DDL to create or update column descriptions in SQL Server.
' Notes     - Drops existing description if necessary
'           - Checks to make sure column exists before trying to add description
'---------------------------------------------------------------------------------------
'
Function UpdateColDescDDL(TableName As String, _
                          ColumnName As String, _
                          Description As String, _
                          Optional SchemaName As String = "dbo") As String
    Dim s As String

    s = s & "--------------------------------------------------------" & vbNewLine
    s = s & "-- " & SchemaName & "." & TableName & "." & ColumnName & ": " & Description & vbNewLine
    s = s & "Declare " & vbNewLine
    s = s & " @SchemaName  nvarchar(max) = " & Qs(SchemaName) & vbNewLine
    s = s & ",@TableName   nvarchar(max) = " & Qs(TableName) & vbNewLine
    s = s & ",@ColName     nvarchar(max) = " & Qs(ColumnName) & vbNewLine
    s = s & ",@Description sql_variant   = " & Qs(Description) & vbNewLine
    s = s & vbNewLine
    s = s & "If Exists (" & vbNewLine
    s = s & "      Select 1" & vbNewLine
    s = s & "      From fn_listextendedproperty('MS_Description'" & vbNewLine
    s = s & "        , 'SCHEMA', @SchemaName" & vbNewLine
    s = s & "        , 'TABLE', @TableName" & vbNewLine
    s = s & "        , 'COLUMN', @ColName" & vbNewLine
    s = s & "      )" & vbNewLine
    s = s & "    )" & vbNewLine
    s = s & "        exec sp_DropExtendedProperty 'MS_Description'" & vbNewLine
    s = s & "          , 'SCHEMA', @SchemaName" & vbNewLine
    s = s & "          , 'TABLE', @TableName" & vbNewLine
    s = s & "          , 'COLUMN', @ColName" & vbNewLine
    s = s & "If Exists (" & vbNewLine
    s = s & "      Select 1" & vbNewLine
    s = s & "      From INFORMATION_SCHEMA.COLUMNS" & vbNewLine
    s = s & "      Where TABLE_NAME = @TableName" & vbNewLine
    s = s & "        And COLUMN_NAME = @ColName" & vbNewLine
    s = s & "      )" & vbNewLine
    s = s & "        exec sp_AddExtendedProperty 'MS_Description', @Description" & vbNewLine
    s = s & "           , 'SCHEMA', @SchemaName" & vbNewLine
    s = s & "           , 'TABLE', @TableName" & vbNewLine
    s = s & "           , 'COLUMN', @ColName" & vbNewLine
    s = s & "--======================================================" & vbNewLine
    
    UpdateColDescDDL = s
End Function

'source: https://nolongerset.com/quoth-thy-sql-evermore/
Private Function Qs(Text As Variant) As String
    Const QtSingle As String = "'"
    
    'We replace single quotes in the string with escaped single quotes so that data
    '  containing double quotes do not blow up our T-SQL statements
    If IsNull(Text) Or IsEmpty(Text) Then
        Qs = "Null"
    Else
        Dim s As String
        s = Text
        Qs = QtSingle & Replace(s, QtSingle, QtSingle & QtSingle) & QtSingle
    End If
End Function

To use the above code, you call it like so:

You can then copy and paste the above code into SQL Server Management Studio and execute it against the target database.  You can also execute the code directly from VBA using ADO or by updating the SQL property of an existing pass-through query.  

Here's a selectable text version of the above T-SQL:

--------------------------------------------------------
-- dbo.Account.AccountID: The table's autonumber primary key.
Declare 
 @SchemaName  nvarchar(max) = 'dbo'
,@TableName   nvarchar(max) = 'Account'
,@ColName     nvarchar(max) = 'AccountID'
,@Description sql_variant   = 'The table''s autonumber primary key.'

If Exists (
      Select 1
      From fn_listextendedproperty('MS_Description'
        , 'SCHEMA', @SchemaName
        , 'TABLE', @TableName
        , 'COLUMN', @ColName
      )
    )
        exec sp_DropExtendedProperty 'MS_Description'
          , 'SCHEMA', @SchemaName
          , 'TABLE', @TableName
          , 'COLUMN', @ColName
If Exists (
      Select 1
      From INFORMATION_SCHEMA.COLUMNS
      Where TABLE_NAME = @TableName
        And COLUMN_NAME = @ColName
      )
        exec sp_AddExtendedProperty 'MS_Description', @Description
           , 'SCHEMA', @SchemaName
           , 'TABLE', @TableName
           , 'COLUMN', @ColName
--======================================================

Building block

Think of this function as a building block.  Its real value is in the possibilities it opens up for integrating with a more comprehensive solution.  For example, you could use this function to transfer column descriptions from a front-end linked table to its corresponding source table in SQL Server.

Image by Bessi from Pixabay