Cacher is the code snippet organizer for pro developers

DECLARE @propertyAlias NVARCHAR(50);
DECLARE @search NVARCHAR(50);

SET @propertyAlias = 'bodyText';
SET @search = 'whatever';

SELECT
	n.id,
	n.path,
	n.text
FROM
	cmsPropertyData AS pd
	INNER JOIN umbracoNode AS n ON n.id = pd.contentNodeId
	INNER JOIN cmsDocument AS d ON n.id = d.nodeId
	INNER JOIN cmsPropertyType AS t ON t.id = pd.propertytypeid
WHERE
	d.newest = 1 AND d.versionId = pd.versionId
	AND t.Alias = @propertyAlias
	AND
	(
		pd.dataNvarchar LIKE ('%' + @search + '%')
		OR
		pd.dataNtext LIKE ('%' + @search + '%')
	)
;