4

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 |
2
  • So you essentially want to parse a character string and convert it to a lookup table, is that correct?
    – Andriy M
    Mar 19, 2018 at 9:48
  • @AndriyM yes, that's correct. Mar 19, 2018 at 14:54

1 Answer 1

4

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.