How to find Pages and Templates that use Scaffolding macros from database

This article is for the Server/Data Center platform. The SQL provided is for PostgreSQL. For other Database platforms, the SQL might need to be modified.

While we can use Confluence’s default “Macro usage statistics” to find the pages that use the macros, the information retrieved can’t be used as a report, and it does not include space templates.

This article provides easy-to-copy SQL to retrieve the information directly from the database, which can be modified to include more details.

\uD83D\uDCD8 Instructions

This SQL will provide a list of impacted page authors and the relevant page titles that currently contain Scaffolding macros:

List of pages with Scaffolding macros

SELECT s.spacekey, s.spacename, c.title AS "Page Title", um.username
FROM content c, spaces s, user_mapping um 
LEFT JOIN cwd_user cu ON um.username = cu.user_name
WHERE c.spaceid = s.spaceid AND c.contenttype = 'PAGE' AND c.prevver IS null AND c.content_status = 'current'
AND c.creator = um.user_key
AND c.title in (SELECT c.title 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="live-template"%'
		OR bc.body LIKE '%ac:name="text-data"%'
		OR bc.body LIKE '%ac:name="list-data"%'
		OR bc.body LIKE '%ac:name="table-data"%'
		OR bc.body LIKE '%ac:name="user-options"%'
		OR bc.body LIKE '%ac:name="date-data"%'
		OR bc.body LIKE '%ac:name="hidden-data"%'
		OR bc.body LIKE '%ac:name="list-option"%'
		OR bc.body LIKE '%ac:name="repeating-data"%'
		OR bc.body LIKE '%ac:name="number-data"%'
		OR bc.body LIKE '%ac:name="content-options"%'
		OR bc.body LIKE '%ac:name="get-data"%'
		OR bc.body LIKE '%ac:name="excerpt-data"%'
		OR bc.body LIKE '%ac:name="eval-data"%'
		OR bc.body LIKE '%ac:name="set-data"%'
		OR bc.body LIKE '%ac:name="attachment-options"%'
		OR bc.body LIKE '%ac:name="label-options"%'
		OR bc.body LIKE '%ac:name="group-data"%')
ORDER BY s.spacekey, c.title

This SQL will provide a list of impacted page authors and the relevant template titles that currently contain Scaffolding macros:

List of templates using Scaffolding macros

SELECT s.spacekey, s.spacename, p.templatename AS "Template Title", um.username
FROM pagetemplates p 
LEFT JOIN spaces s ON p.spaceid = s.spaceid
LEFT JOIN user_mapping um ON p.creator = um.user_key
WHERE p.prevver IS null
AND p.content LIKE '%ac:name="live-template"%'
		OR p.content LIKE '%ac:name="text-data"%'
		OR p.content LIKE '%ac:name="list-data"%'
		OR p.content LIKE '%ac:name="table-data"%'
		OR p.content LIKE '%ac:name="user-options"%'
		OR p.content LIKE '%ac:name="date-data"%'
		OR p.content LIKE '%ac:name="hidden-data"%'
		OR p.content LIKE '%ac:name="list-option"%'
		OR p.content LIKE '%ac:name="repeating-data"%'
		OR p.content LIKE '%ac:name="number-data"%'
		OR p.content LIKE '%ac:name="content-options"%'
		OR p.content LIKE '%ac:name="get-data"%'
		OR p.content LIKE '%ac:name="excerpt-data"%'
		OR p.content LIKE '%ac:name="eval-data"%'
		OR p.content LIKE '%ac:name="set-data"%'
		OR p.content LIKE '%ac:name="attachment-options"%'
		OR p.content LIKE '%ac:name="label-options"%'
		OR p.content LIKE '%ac:name="group-data"%'
ORDER BY s.spacekey, p.templatename;

How to find Pages and Templates that use Scaffolding macros from database

\uD83D\uDCD8 Instructions

\uD83D\uDCCB Related articles