How to find pages using a specific label in Confluence via SQL
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Confluence administrators may want to audit Label usage on their Confluence instance. This can be done by using the labelText: search field.
The alternative approach is by locating a labeled page or post and choosing any label to access the Labeled Content page.
Issue
If viewing and editing restrictions are applied to a page, the page will not be included on the search performed when viewing the Labeled Content pages. This also occurs when accessing with a user who belongs to confluence-administrator group.
In these case, it is possible to get this information from the database using the SQL statements below.
Solution
Make sure you have a backup of your database before performing any SQL query.
List of pages that don't have any label:
SELECT c.title,s.spacename
FROM content c
JOIN spaces s ON s.spaceid = c.spaceid
LEFT JOIN content_label cl ON cl.contentid = c.contentid
WHERE c.contenttype = 'PAGE'
AND c.prevver IS NULL
AND c.content_status = 'current'
AND cl.contentid IS NULL
ORDER BY s.spacename,c.title;SELECT c.title, s.spacename
FROM confluence.CONTENT c
JOIN confluence.SPACES s ON s.spaceid = c.spaceid
LEFT JOIN confluence.CONTENT_LABEL cl ON cl.contentid = c.contentid
WHERE c.contenttype = 'PAGE'
AND c.prevver IS NULL
AND c.content_status = 'current'
AND cl.contentid IS NULL
ORDER BY s.spacename, c.title;SELECT c.TITLE, s.SPACENAME
FROM confluence.dbo.CONTENT c
JOIN confluence.dbo.SPACES s ON s.SPACEID = c.SPACEID
LEFT JOIN confluence.dbo.CONTENT_LABEL cl ON cl.CONTENTID = c.CONTENTID
WHERE c.CONTENTTYPE = 'PAGE'
AND c.PREVVER IS NULL
AND c.CONTENT_STATUS = 'current'
AND cl.CONTENTID IS NULL
ORDER BY s.SPACENAME, c.TITLE;List of pages that have a specific label:
SELECT c.title,s.spacename,l.name as label
FROM content c
JOIN spaces s ON s.spaceid = c.spaceid
JOIN content_label cl ON cl.contentid = c.contentid
JOIN label l ON l.labelid = cl.labelid
WHERE l.name = 'demo'
AND c.contenttype = 'PAGE'
AND c.content_status = 'current'
AND c.prevver IS NULL;SELECT c.title, s.spacename, l.name AS label
FROM confluence.CONTENT c
JOIN confluence.SPACES s ON s.spaceid = c.spaceid
JOIN confluence.CONTENT_LABEL cl ON cl.contentid = c.contentid
JOIN confluence.LABEL l ON l.labelid = cl.labelid
WHERE l.name = 'label1'
AND c.contenttype = 'PAGE'
AND c.content_status = 'current'
AND c.prevver IS NULL;SELECT c.TITLE, s.SPACENAME, l.NAME AS label
FROM confluence.dbo.CONTENT c
JOIN confluence.dbo.SPACES s ON s.SPACEID = c.SPACEID
JOIN confluence.dbo.CONTENT_LABEL cl ON cl.CONTENTID = c.CONTENTID
JOIN confluence.dbo.LABEL l ON l.LABELID = cl.LABELID
WHERE l.NAME = 'label'
AND c.CONTENTTYPE = 'PAGE'
AND c.CONTENT_STATUS = 'current'
AND c.PREVVER IS NULL;