Obtain ALL parents for a page using SQL in SQL Server

[john davison]

Im trying create a report that pulls out all pages not updated in the last month and I'm looking to try and show All Parents ( if any) of those pages.

Anyone out there who has tried this and suceeded?

Thanks

[Betsy Walker {Appfire} · Accepted solution]

I have a SQL query from a few years ago that found all descendants, so I imagine the same approach would work for finding ancestors/parents. You would also need to add a WHERE clause that looks for lastmoddate being outside the last 30 days.

-- Set variable representing the page whose descendants you want to find
DECLARE  @contentid NUMERIC(19, 0)
SET @contentid = 65106350				
-- Declare table to collect all descendants under a particular page
DECLARE @descendants TABLE
(
    contentid   NUMERIC(19, 0) NOT NULL PRIMARY KEY,
    tltitle     VARCHAR(255)       NULL,
    lltitle     VARCHAR(255)       NULL
)
-- Load @Descendants table with direct (i.e. top-level) descendants
INSERT @descendants
(
	contentid,
	tltitle,
	lltitle
)
SELECT C.contentid,
		C.title,
		C.title
FROM content C
WHERE parentid = @contentid
-- Load @Descendants table with indirect (lower-level) descendants, ensuring
-- no duplicates
WHILE @@rowcount <> 0
BEGIN
    INSERT @descendants
    (
    contentid,
    tltitle,
    lltitle
    )
    SELECT C.contentid,
        D.tltitle,
        C.title
    FROM @descendants   D 
    INNER JOIN content  C ON C.parentid = D.contentid
    WHERE NOT EXISTS(SELECT 1
                     FROM @descendants D2
                     WHERE D2.contentid = C.contentid)
END
-- Show results (abbreviated)
SELECT D.tltitle AS ParentPage, D.lltitle AS PageTitle,
	'http://wikiurl.com/pages/viewpage.action?pageId=' + CONVERT(VARCHAR(10),D.contentid) AS ViewPageURL,
	'http://wikiurl.com/pages/editpage.action?pageId=' + CONVERT(VARCHAR(10),D.contentid) AS EditPageURL
FROM @descendants D 
ORDER BY D.tltitle, D.lltitle

[joanna ransome-wallis]

You can use this plugin, but it will only show page views from the day the plugin is added: https://marketplace.atlassian.com/plugins/net.customware.confluence.plugin.tracking/server/overview