Get All Sitefinity 3.7 URL's (SQL Server 2008)

Yes, my template is still messed up and probably will be until July.

Problem:

You need to get all of the URL's of your pages in Sitefinity via a database query.

Solution:

Recursive queries are your friend.


WITH Pages (ID, ParentID, Url, Title) AS
(
SELECT
base.ID, base.ParentID, CONVERT(NVARCHAR(250), '/' + base.Name), content.Title
FROM sf_PageBase base (NOLOCK)
INNER JOIN sf_CmsPageContent content (NOLOCK) on base.ID = content.ID
WHERE base.ParentID IS NULL

UNION ALL

SELECT
base.ID, base.ParentID, CONVERT(NVARCHAR(250), Pages.Url + '/' + base.Name), content.Title
FROM sf_PageBase base (NOLOCK)
INNER JOIN sf_CmsPageContent content (NOLOCK) on base.ID = content.ID
INNER JOIN Pages ON base.ParentID = Pages.ID
)
SELECT ID, Url, Title FROM Pages
ORDER BY Url;