0121 31 45 374
Qoute Icon

View the property values in SQL for latest version of a page in Umbraco

Tim

I've previously blogged about how to download any Umbraco document as a CSV from SQL but we hit a slightly different issue today and needed to view the values for a property in SQL.

By default Umbraco stores each property as it's own row in the cmsPropertyData table which is versioned (so you can roll back). That can make it a little tricky to pull out the latest version in a rush as you have to reference the cmsContentVersion table, work out which one was the most recent etc.

This is a quick SQL Script which will pull out the most recent values for a given page (in my case 8220). If you're not sure what the page id is and are running Umbraco 7+ then you can just get it from the url. Otherwise check the Properties tab.


?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
--Set this to the id of the node you want to view
DECLARE @NodeId INT = 8220
 
SELECT
    v.VersionDate
  , pt.[Name] AS [Property Name]
  , pd.propertytypeid
  , pd.dataInt
  , pd.dataDate
  , pd.dataNvarchar
  , pd.dataNtext
  , pd.dataDecimal
FROM
    (
        SELECT
            cv.ContentId
          , cv.VersionId
          , cv.VersionDate
          , ROW_NUMBER() OVER (PARTITION BY cv.ContentId ORDER BY cv.VersionDate DESC) AS rn
        FROM
            cmsContentVersion cv
        WHERE
            cv.ContentId = @NodeId
    ) v
        LEFT JOIN umbracoNode n ON n.id = v.ContentId
        LEFT JOIN cmsPropertyData pd ON n.id = pd.contentNodeId AND v.VersionId = pd.VersionId
        LEFT JOIN cmsPropertyType pt ON pd.propertytypeid = pt.id
WHERE
    v.rn = 1
< Back to Blog

Liked this post? Got a suggestion? Leave a comment