1-888-365-2779
+1-888-365-2779
Try Now
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
    Registered:
    06 Aug 2012
    22 Sep 2012
    Link to this post
    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
  2. Jochem Bökkers
    Jochem Bökkers avatar
    787 posts
    Registered:
    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 */
    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



    Answered
  3. Olivier
    Olivier avatar
    31 posts
    Registered:
    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!

    Cheers!
  4. Dan Sorensen
    Dan Sorensen avatar
    256 posts
    Registered:
    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