Find Sitecore Item Path with the help of Item Id using SQL Script

Sometimes it's difficult to find the Item path if you are working on multi-site and their Sitecore Search is not working.

The same issue I was facing today, tried as much as possible. And came up with one approach to write SQL Script for this.


For this only need to write one SQL Function and then run the query with Item Id for which you are finding the item path.


Below is the SQL Function which you have to create in the Instance SQL Database where you are finding Item Information

USE [DBNAME]
GO
/****** Object:  UserDefinedFunction [dbo].[GetItemPath]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetItemPath] 
(
 @ItemID [uniqueidentifier],
 @RootItemPath [uniqueidentifier]
)
RETURNS varchar(MAX)
AS
BEGIN
 DECLARE @Result varchar(MAX);

 with scpath(Name, ParentID, id)
 as
 (
  select Cast(a.Name as varchar(MAX)), a.ParentID, a.ID
  from [Items] a
  where a.ID = @ItemID
  union all
  select Cast(b.Name + '/' + c.Name as varchar(MAX)), b.ParentID, b.ID
  from [Items] b
    inner join scpath c on b.ID = c.ParentID
  where c.ParentID is not null
 )

 select top 1 @Result = '/' + d.Name  from scpath d
  where d.ID = @RootItemPath
 RETURN @Result

END


Once function created now run the below script with Item Id

SELECT [Name], [dbo].[GetItemPath] (ID,'{11111111-1111-1111-1111-111111111111}') AS ItemPath 
 FROM [dbo].[Items]
 WHERE [ID] = '{45BC606D-21CE-4D03-B204-9EBAC0ABDF0B}'

Hope it will help you, Enjoy your day :-)