How to find all pages and spaces that use a specific macro via SQL
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the server and data center platforms.
Summary
For purposes of troubleshooting or auditing, Confluence administrators may wish to find pages or spaces that contain a specific macro. This knowledge base article takes on this task by querying the Confluence 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.
In all queries below, please replace <macro_name> with the actual name of your macro.
Find all current pages or blog posts that contain the macro (historical pages are not considered):
SELECT c.contentid, c.contenttype, c.title, s.spacekey
FROM CONTENT c
JOIN BODYCONTENT bc
ON c.contentid = bc.contentid
JOIN SPACES s
ON c.spaceid = s.spaceid
WHERE c.prevver IS NULL
AND c.contenttype IN ('PAGE', 'BLOGPOST')
AND bc.body LIKE '%ac:name="<macro_name>"%';Find all spaces that have current content that uses a macro:
SELECT DISTINCT s.spaceid, s.spacekey, s.spacename
FROM CONTENT c
JOIN BODYCONTENT bc
ON c.contentid = bc.contentid
JOIN SPACES s
ON c.spaceid = s.spaceid
WHERE c.prevver IS NULL
AND c.contenttype IN ('PAGE', 'BLOGPOST')
AND bc.body LIKE '%ac:name="<macro_name>"%';