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