SQL Code Snippet for Getting Paths of Sitecore Items

This post is based on my answer of SO question regarding getting Sitecore items paths.

It is rare situation when you need to use recursive SQL queries, I decided to practice.

with Items1 (nm, id1, pathstr)
as (select Name, id, cast('/sitecore' as nvarchar(MAX))
   from Items
   where ParentID = '00000000-0000-0000-0000-000000000000'
union all
   select Items.Name, Items.id, Items1.pathstr +'/'+ Items.Name
   from Items
     inner join Items1 on Items1.id1 = Items.ParentID)
select id1 as ItemId, nm as Name, pathstr as [Path]
from Items1

This query allow you to get ids and paths for all items in your Sitecore master(web, core) database.