Is there a quick way of checking how many child items there are by querying the sitecore master db?


You can create a view that captures the item paths. Once you have that view, it's fairly easy to query that based on a part of the path to find children.

All sql and info can be found on this blog: http://chowson.github.io/sitecore-item-paths-in-sql-queries/

And just in case it gets deleted, here it is:

CREATE VIEW [dbo].[ItemsPath]
    WITH ItemsTable (ItemPath, ID, Name, TemplateID, MasterID, ParentID, Created, Updated)
        SELECT CAST('/' + base.Name AS nvarchar(MAX)) as ItemPath,
            base.ID, base.Name, base.TemplateID, base.MasterID, base.ParentID, base.Created, base.Updated

        FROM Items as base
        WHERE base.ID = '11111111-1111-1111-1111-111111111111'  

        UNION ALL

        SELECT CAST(ItemPath + '/' + child.Name AS nvarchar(MAX)),
            child.ID, child.Name, child.TemplateID, child.MasterID, child.ParentID, child.Created, child.Updated

        FROM ItemsTable as parent 
        INNER JOIN Items as child 
            ON child.ParentID = parent.ID 
    SELECT ItemPath, ID, Name, TemplateID, MasterID, ParentID, Created, Updated
    FROM ItemsTable

And to use it:

--Returns all descendants of the /sitecore/template item
FROM ItemsPath
WHERE ItemPath LIKE '/sitecore/templates%'
| improve this answer | |
  • 1
    Add / between templates and %. Otherwise you return descendants count + 1 (for the item itself). And if there was another item like /sitecore/templates 2 it would count it as well. Anyway, very good answer. +1 – Marek Musielak Apr 26 '18 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.