How to find out how many items are in media folder by querying the db

Question

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

Answer 1

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]
AS
    WITH ItemsTable (ItemPath, ID, Name, TemplateID, MasterID, ParentID, Created, Updated)
    AS
    (
        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
GO

And to use it:

--Returns all descendants of the /sitecore/template item
SELECT * 
FROM ItemsPath
WHERE ItemPath LIKE '/sitecore/templates%'