How to find pages using a specific label in Confluence via SQL

Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.

Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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.

Language

Query pages with no labels

Query pages with specific label

PostgreSQL

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,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;

MySQL

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, 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;

SQL Server

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;

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;

Updated on April 8, 2025
Platform
Data Center only
Product
Confluence Data Center
On this pageSummarySolution

Still need help?

The Atlassian Community is here for you.
Ask the Community