Get all URLs in Umbraco using SQL

Use the below SQL script to get all the Urls in your Umbraco project.

; WITH PathXml AS (

/* -- This gives nodes with their 'urlName' property
 -- not in recycle bin,
 -- level > 1 which excludes the top level documents which are not included in the url */
 SELECT
 nodeId,
 cast([xml] as xml).query('data(//@urlName[1])').value('.', 'varchar(max)') AS Path
 FROM cmsContentXml x
 JOIN umbracoNode n ON x.nodeId = n.id AND n.trashed = 0 AND n.level > 1
)

SELECT
 u.id,
 u.path,
 '/' +
 /* This is to get the top level document */
 IsNull((SELECT
 pl.Path + '/'
 FROM PathXml pl
 WHERE ',' + u.path + ',' LIKE '%,' + CAST(pl.nodeId AS VARCHAR(MAX)) + ',%'
 ORDER BY CHARINDEX(',' + CAST(pl.nodeId AS VARCHAR(MAX)) + ',',
 ',' + u.path + ',')

FOR XML PATH('')),
 '') AS Url,
 u.text PageName
FROM umbracoNode u
WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' /* Document node */
AND trashed = 0
ORDER BY 3 /* Url */

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.