Published: 10 December 2015 At some point you will need to search for some data in the Sitecore database. 

Not for selecting out data for a production website (you should be using Lucene for this). 

But if you are migrating data from an existing site or working with data that gets auto generated from say a booking feed. You may want to search the data base to find particular items with a field value and so on. 

So I thought I would share this SQL snippet that I’ve found useful from time to time.

SELECT i.ID AS ItemID, i.Name AS ItemName, FieldItem.Name AS FieldName, f.Value AS FieldValue,
TemplateItem.ID AS TemplateID, TemplateItem.Name AS TemplateName,
SUBSTRING(f.Value, 0, 5) AS TheYear, SUBSTRING(f.Value, 5, 2) AS TheMonth, SUBSTRING(f.Value, 7, 2) AS TheDay,
CONVERT(DateTime, SUBSTRING(f.Value, 0, 5) + '-' + SUBSTRING(f.Value, 5, 2) + '-' + SUBSTRING(f.Value, 7, 2) + ' 00:00:00') AS PublishDate
FROM dbo.Items i
INNER JOIN dbo.Fields f ON i.ID = f.ItemId
INNER JOIN dbo.Items FieldItem ON FieldItem.ID = f.FieldId
INNER JOIN dbo.Items TemplateItem ON TemplateItem.ID = i.TemplateID
TemplateItem.ID = 'DBC2FA50-01F7-4840-9135-E08AC9B7C512'
AND FieldItem.Name = '__Publish'

You can change TemplateItem.ID to the guide of the template type you want to return. If you want to search fields for a specific value you can use f.value.

The above example out of the box returns the Published Date for the various items with a template id of DBC2FA50-01F7-4840-9135-E08AC9B7C512.

I’ve found it handy for searching for ‘bum’ data, so hopefully you will find it handy too.


Richard Brisley

Drum Recommends 2020   Wirehive_Partner     rar   SagittariusAgency  PoweredbySitecoreGooglePartnerFooter

Page Name: {% PageName %}

Page Template: {% PageTemplate %}

CampaignID: {% AgentReferrer.ID %}

CampaignName: {% AgentReferrer.Name %}

CampaignPhone: {% AgentReferrer.Phone %}

Item Location: {% PageLocation %}

Search Session Exists: False