Sitecore Item Paths in SQL Queries
Developing or debugging Sitecore applications can quite often lead to executing SQL queries to try and work out how something works or where the issue lies.
One piece of information that cannot be easily accessed in an SQL query is the hierarchy of items because the item path isn't saved as a field value within the database. Item hierarchy is instead stored by each row in the Items table storing the ID of its parent in the ParentID column.
Running the following SQL statement will create a view which is a copy of the Items table but with an additional column defining the item's full path:
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
Running the query below will return the following result:
SELECT * FROM ItemsPath
Once the view is created, queries can be run like the following:
--Returns all descendants of the /sitecore/template item SELECT * FROM ItemsPath WHERE ItemPath LIKE '/sitecore/templates%' --Returns all rows in the WorkflowHistory table for all --descendants of the news item (/sitecore/content/Home/News) SELECT WorkflowHistory.* FROM WorkflowHistory JOIN dbo.ItemsPath ON ItemsPath.ID = WorkflowHistory.ItemID WHERE ItemPath LIKE '/sitecore/content/Home/News%'