Using SQL Server to find items in Sitecore Database .

UsingSQLServer

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

WHERE

TemplateItem.ID = 'DBC2FA50-01F7-4840-9135-E08AC9B7C512'

AND FieldItem.Name = '__Publish'

ORDER BY PublishDate DESC

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.

want to speak to one of our experts?

Richard Brisley
Richard Brisley
Tech Lead
Richard is the longest standing member of the Sagittarius team, he works tirelessly to support the development and side-facing team with problem solving and pitches alike. His skills as a .NET programmer and database administrator have been paramount to the success of Sagittarius and our continued success. 

In 2016 and again in 2019 Richard was recognise in the BIMA 100 awards for his outstanding work in Tech, his passion for digital and his contribution to the industry. 

Richard Brisley

Richard Brisley

10 Dec 2015 - 5 minute read
share this

stay in the know, stay ahead.

Get the latest from the agency, including news, events and expert content.
explore services in the article
find out what we can do for you
read some of our case studies