Challenge:
We need to know in how many Sitecore items word “keyword” is present. This requirement arrived because client had a contract with one third party service and now they have planned to discard the contract with that party and change the service with other party. Now these third party services provide some script which we add in Sitecore item and now for all the items where we have script used by first service need to be replaced with another. And the site is very big and we can’t check each and every item where the “keyword” is present.
So after discussing with Urvesh, we came up with an idea to fetch from Database directly.
Solution:
Ultimately sitecore stores the value in Database into three tables — Versioned, Unversioned and Shared fields. So we wrote a query which will hit all the three tables and provide us the item ids where the keyword is present. The site is in many languages and if in any language version the keyword is present then it will be captured. This saved enormous time and money. 🙂 We’ve heavily used this utility for providing the report to the Content Authors and Migration. Additionally we have created many such tools which can update the items in bulk, we will share it soon.
Database Queries:
1) Versioned Fields
var query = @”Select [ID], [Name] FROM [dbo].[Items]
Where [ID] in (Select DISTINCT [ItemId] From [dbo].[VersionedFields] Where [FieldId] in (SELECT [ID] FROM [Items] Where Name NOT LIKE ‘\_\_%’ ESCAPE ‘\’) AND Value like ‘%” + strKeyword + “%’)”
2) Shared Fields
var query = @”Select [ID], [Name] FROM [dbo].[Items]
Where [ID] in (Select DISTINCT [ItemId] From [dbo].[SharedFields] Where [FieldId] in (SELECT [ID] FROM [Items] Where Name NOT LIKE ‘\_\_%’ ESCAPE ‘\’) AND Value like ‘%” + strKeyword + “%’)”
3) Unversioned Fields
var query = @”Select [ID], [Name] FROM [dbo].[Items]
Where [ID] in (Select DISTINCT [ItemId] From [dbo].[UnversionedFields] Where [FieldId] in (SELECT [ID] FROM [Items] Where Name NOT LIKE ‘\_\_%’ ESCAPE ‘\’) AND Value like ‘%” + strKeyword + “%’)”
We wrote an aspx code file and deployed on the server. This utility will provide us the report which we can export easily.
Download: Click Me (Right click and Save Link As)
Screenshot:
Thanks to Urvesh who worked on this!
Let us know if you have found any other solution.
Happy Sitecoring! 🙂