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

1. 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;

2. Run the following query, replacing these labels one by one with the <label_title> to get pages containing the Content by Label macro 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_%';

Related:

• How to find all pages and spaces that use a specific macro via SQL