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

Owned by Julian Rachmadiputra, created
Last updated: Jul 30, 2024
2 min read

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 you 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 does not include space templates.

This article provides easy-to-copy SQL to retrieve information directly from the database. You can also modify the SQL to get even more details if you need.

 Instructions

This SQL will provide a list of impacted page authors and the relevant page titles that currently contain 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:

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;

 Related articles

Product Documentation