SSMS – Query variables from Table Design Mode > Column Description

Posted on

Question :

I need to query the description of the Sales.SalesOrderHeader.Status column from the AdventureWorks OLTP, which looks like this:

| SalesOrderID | RevisionNumber | OrderDate  | ShipDate   | Status |
| 43659        | 8              | 2011-05-31 | 2011-06-12 | 5      |

Instead of the status column being linked as a foreign key to a status table with a description column, the status description appears in the Table Design Mode’s column description:

enter image description here

The closest script I’ve been able to find to query the status description is from Devioblog: Retrieving Table and Column descriptions in SQL Server.

  , AS ColumnName
  , AS PropertyName
  , ep.value AS Description
FROM sys.objects
  INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
    CROSS APPLY fn_listextendedproperty(default,
              'SCHEMA', schema_name(schema_id),
              'COLUMN', ep
ORDER BY, sys.columns.column_id


TableName        | ColumnName | PropertyName   | Description
SalesOrderHeader | Status     | MS_Description | Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled

The problem with this is that all of the variables are housed as one value in the ep.value AS Description field, but I need to query them separately based on what’s in the Sales.SalesOrderHeader.Status column. Pseudo-query:

SELECT ep.value
FROM sys.columns
LEFT OUTER JOIN Sales.SalesOrderHeader ON sys.columns.ep.value=Sales.SalesOrderHeader.Status

Desired Result:

| Status  |
| Shipped |

Answer :

I’d suggest to create either an actual table with the status and id itself and create a FK to the SalesOrderHeader.Status table and my code below will convince you 🙂

So below would be a formatted implementation of what you’ll probably need, as you can see it’s a long winding road just to join a column.You may modify below and put it inside a udf if you don’t want to go to an actual table.

;WITH src
AS (
    --#1 get extended info description meta attribute
    SELECT                AS TableName,
                 AS ColumnName,
                          AS PropertyName,
           CAST(ep.value AS NVARCHAR(255)) AS Description
    FROM sys.objects
        INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
        CROSS APPLY fn_listextendedproperty(
                                           ) ep
    WHERE = 'SalesOrderHeader' AND
 = 'Status'

src2 AS (
   --#2 Retain only status description to be extracted later
   SELECT SUBSTRING(src.Description, CHARINDEX('.', src.Description) + 1, 255) descd 
   FROM src
   --#3 extract id and description
SELECT CAST(SUBSTRING(x.StatusDesc, 1, CHARINDEX('=', x.StatusDesc) - 1) AS TINYINT) AS StatusId,
       LTRIM(RTRIM(SUBSTRING(x.StatusDesc, CHARINDEX('=', x.StatusDesc) + 1, 255)))  AS StatusDesc
    SELECT LTRIM(RTRIM(m.n.value('.[1]', 'varchar(8000)'))) AS StatusDesc
        --#3.1 generate rows from the delimited data of status description
        SELECT CAST('<XMLRoot><RowData>' + REPLACE(src2.descd, ';', '</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
        FROM src2
    )                                           t
        CROSS APPLY x.nodes('/XMLRoot/RowData') m(n)
) x;

Leave a Reply

Your email address will not be published. Required fields are marked *