View the property values in SQL for latest version of a page in Umbraco
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.
--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