SSMS – Query Variables from Table Design Mode Column Description

sql serverssmst-sql

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.

SELECT sys.objects.name AS TableName
  , sys.columns.name AS ColumnName
  , ep.name 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),
              'TABLE', sys.objects.name,
              'COLUMN', sys.columns.name) ep
WHERE sys.objects.name='SalesOrderHeader'
  AND sys.columns.name='Status'
ORDER BY sys.objects.name, sys.columns.column_id

Result:

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 |

Best 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 sys.objects.name                AS TableName,
           sys.columns.name                AS ColumnName,
           ep.name                         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(
                                               DEFAULT,
                                               'SCHEMA',
                                               SCHEMA_NAME(schema_id),
                                               'TABLE',
                                               sys.objects.name,
                                               'COLUMN',
                                               sys.columns.name
                                           ) ep
    WHERE sys.objects.name = 'SalesOrderHeader' AND
          sys.columns.name = '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
FROM
(
    SELECT LTRIM(RTRIM(m.n.value('.[1]', 'varchar(8000)'))) AS StatusDesc
    FROM
    (
        --#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;
Related Question