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

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

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 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 blogposts 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>"%';

Related:

Last modified on May 2, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.