How to query the database to find pages using a specific label in Confluence
Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.
Confluence administrators may want to audit Label usage on the instance. This can be done by using the labelText: search field.
The alternative way is by locating a labeled page or post and choosing any label to access the Labeled Content page.
If Viewing and editing restrictions is applied to any page, that page will not be included on the search performed or when viewing the Labeled Content pages even when accessing with a confluence-administrator user.
In this case, it is possible to audit this information from the database using the queries below.
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;
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.prevver IS NULL;