Getting a list of pages per space [on-premises]

This article contains information on how to get the pages per space for an on-premises Confluence instance.

When talking about pages in this article blog posts are meant as well.

Getting pages per space

This script returns a list of page metadata like content ID and title.

Run the following SQL script on the Confluence database. IMPORTANT: Replace MYSPACEKEY with the key of the space you want to get the page info for:

select contentid, contenttype, title, content_status, spacekey
from content left join spaces on content.spaceid = spaces.spaceid
where (contenttype='PAGE' or contenttype='BLOGPOST') and prevver is null and content_status='current' and spacekey='MYSPACEKEY';

Note: the space key is case SeNsItIvE.

Getting the page count per space

This script returns the page count for a space.

Run the following SQL script on the Confluence database. IMPORTANT: Replace MYSPACEKEY with the key of the space you want to get the page count for:

select count(*)
from content left join spaces on content.spaceid = spaces.spaceid
where (contenttype='PAGE' or contenttype='BLOGPOST') and prevver is null and content_status='current' and spacekey='MYSPACEKEY';

Note: the space key is case SeNsItIvE.

Getting the most recent page change per space

This script returns the timestamp of the most recently modified page or blog post for every space.

Run the following SQL script on the Confluence database.

SELECT
    spaces.spacekey,
    MAX(content.lastmoddate) AS most_recent_change_date
FROM
    content
LEFT JOIN
    spaces ON content.spaceid = spaces.spaceid
WHERE
    (content.contenttype = 'PAGE' OR content.contenttype = 'BLOGPOST')
    AND content.prevver IS NULL
    AND content.content_status = 'current'
GROUP BY
    spaces.spacekey
ORDER BY
    most_recent_change_date DESC;