quick sql select for published pages

Hi all
I have done some quick checks on the SQL backend for Sitefinity 5.1.3450 (and this may apply to earlier versions too but I haven't tested those) to write a select statement that fetches all published page urls in a site.   It turns out that the page urls are stored in a table called sf_page_node in a hierarchical structure.  You can fetch your page urls from this table using the Common Table Element (CTE) structure in SQL 2005 onwards, and the following query seems to work nicely:

WITH Pages as
(
 SELECT N.content_id, N.id,CONVERT(uniqueidentifier,NULL) as parent_id, CONVERT(nvarchar(max), '/') + convert(nvarchar(max),N.url_name_) as url
FROM [dbo].sf_page_node N
WHERE url_name_ = 'Pages'

 UNION ALL
 SELECT N.content_id,N.id, parent.id, CONVERT(nvarchar(max),parent.url) +CONVERT(nvarchar(max), '/') + convert(nvarchar(max),N.url_name_) as url
FROM [dbo].sf_page_node N
JOIN Pages parent
ON N.parent_id = parent.id
AND N.approval_workflow_state_ = 'Published'
)
SELECT SUBSTRING(url,7,LEN(url)-6) from Pages 
ORDER BY url 

I hope this helps some of you

Hello,

 Thanks for submitting your resolution to our forums. The same functionality can be achieved via our API.

Thanks Patrick.  I know it is always preferable to use the API if possible, however I required this as a quick solution for an external process which I am using to evaluate the effectiveness of the Sitefinity output cache settings and to monitor uncached page load times.  The only other alternative would have been to write a web service that references the API.  Indeed I am going to look at that solution in any case, because it will help with other areas which involve automated inserts and updates to Sitefinity, something that I would never attempt via direct sql updates.  I only posted this query because it is purely a select statement.  I hope I didn't cause any problems.