More in this section

Forums / Developing with Sitefinity / SQL Query - list all pages with their URL

SQL Query - list all pages with their URL

4 posts, 1 answered
  1. Olivier
    Olivier avatar
    31 posts
    06 Aug 2012
    22 Sep 2012
    Link to this post
    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. 
  2. Jochem Bökkers
    Jochem Bökkers avatar
    787 posts
    13 Aug 2007
    23 Sep 2012
    Link to this post
    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 */
    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
    12.  sf_page_node LEFT OUTER JOIN
    13.  sf_page_node AS sf_page_node_2
    14.  ON
    15.  sf_page_node.parent_id =
    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....


  3. Olivier
    Olivier avatar
    31 posts
    06 Aug 2012
    24 Sep 2012
    Link to this post
    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!

  4. Dan Sorensen
    Dan Sorensen avatar
    256 posts
    13 Aug 2010
    27 Sep 2012
    Link to this post
    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.
4 posts, 1 answered