SQL Query - list all pages with their URL - Sitefinity - Front- & Back-End Development

All Replies

Posted by Community Admin on 22-Sep-2012 00:00

Hi.
I am trying to get a list of all pages (pages that show in sitemap) with their complete URLs. I would like to use a WakeUpScript (VBS file) to wake up these pages on a regular basis so that the site is a bit more responsive.The only way I found so far is to create a widget that will cycle through a SitefinitySiteMapDataSource, but this is not practical since I have to add the widget to a page, get the list, and then remove the page - I don't want this page to be on the site permanently.

So, I am looking for an SQL script to do this.
Thanks,
Olivier

Posted by Community Admin on 23-Sep-2012 00:00

Hey Oliver,

Using the pages.svc would be preferrable, but since you're asking for a SQL statement you can try something like this:

01./* return pages with parent_id */

02.SELECT url_name_, parent_id FROM sf_page_node

03.WHERE (root_id =

04. (

05. SELECT id FROM sf_page_node

06. WHERE (nme = 'FrontendSiteMap'))

07. ) AND

08. (show_in_navigation = 1) AND

09. (render_as_link = 1) AND

10. (node_type = 0) AND

11. (approval_workflow_state_ = N'Published')

That would return the individual url_names of the pages and their parent_id's.
You need to traverse the parent_id's to properly build out the urls though...

For example something like this:

01./* return 1st+2nd level pages with parent url */

02.SELECT

03. _url_parent =

04. CASE WHEN

05. (sf_page_node_2.url_name_ = N'Pages')

06. THEN '/'

07. ELSE sf_page_node_2.url_name_ +'/'

08. END,

09. sf_page_node.url_name_ AS url_page

10.

11.FROM

12. sf_page_node LEFT OUTER JOIN

13. sf_page_node AS sf_page_node_2

14. ON

15. sf_page_node.parent_id = sf_page_node_2.id

16.WHERE

17. (sf_page_node.root_id =

18. (

19. SELECT id

20. FROM

21. sf_page_node AS sf_page_node_1

22. WHERE

23. (nme = 'FrontendSiteMap')

24. )

25. ) AND

26. (sf_page_node.show_in_navigation = 1) AND

27. (sf_page_node.render_as_link = 1) AND

28. (sf_page_node.node_type = 0) AND

29. (sf_page_node.approval_workflow_state_ = N'Published')

In the attachment you'll see the output of those snippets....

Jochem

Posted by Community Admin on 24-Sep-2012 00:00

Thanks a lot Jochem!
I was trying to figure out a bit the DB table structure to find out what part is what but I could not get a clear picture.Your script answers my question, and will also help me understand the DB structure, so I thank you...twice!

Cheers!

Posted by Community Admin on 27-Sep-2012 00:00

You could also create a page titled 'sitemap' (or similar) set it to not show in navigation, place the SitefinitySiteMapDataSource control on it, and then setup a keep alive service to call that page URL.

This thread is closed