How to find a list of pages that contains Content by Label macro and Content by Label is displaying a specific page
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
Some labeled pages can be displayed on other pages using the Content By Label macro. For content maintenance or reporting purposes, Confluence administrators may want to find out on which pages the relevant page is displayed using the Content By Label macro. This knowledge base article takes on this task by querying the database using SQL queries
Solution
The queries below use wildcard searching in order to locate page content that matches a specific pattern for macros. Due to the nature of wildcard searching, these queries may take a long time to execute, and it is recommended to carry these out in a clone of the production database rather than on the production environment itself to avoid any impact to the production environment.
Use the below query and replace <page_title> with the relevant page's title to get the labels of this page.
select c.title as page_title, l.name as label_name from content_label cl join content c on cl.contentid = c.contentid join label l on cl.labelid = l.labelid where c.title = '<page_title>' order by title desc;Run the following query, replacing these labels one by one with the <label_title> to get pages containing the
Content by Labelmacro with the given label used as a CQL filter.select s.spacekey, s.spacename, c.title, c.contentid from content c join bodycontent b on c.contentid = b.contentid join spaces s on c.spaceid = s.spaceid where c.contenttype = 'PAGE' and c.content_status = 'current' and c.prevver is NULL and b.body like '%ac:name="contentbylabel%' and b.body like '%label = _quot_<label_title>_quot_%';