How to find all pages that use SQL for Confluence macros via database

This article helps you find all the pages where the SQL for Confluence macros are added to your confluence instance hosted on the server or data center.

Instructions

Log in as Confluence Administrator and navigate to Cogwheel > General configuration > Administration > Macro Usage:

Under All macros, look out for the App name SQL(Pro Edition) for Confluence:

Usage information using SQL queries:

As the SQL for Confluence has three macros, you must execute the query for each macro to get the macro-level usage information. If the query returns any records, the output includes the Title of the page and SpaceID of the relevant pages.

• For the sql macro, execute the following query:
  

  Select CONTENT.Title, CONTENT.SpaceID from CONTENT, BODYCONTENT
  WHERE PREVVER IS NULL AND (CONTENT.CONTENTTYPE = 'PAGE' OR CONTENT.CONTENTTYPE = 'BLOGPOST')
  AND CONTENT.CONTENTID = BODYCONTENT.CONTENTID
  AND (BODYCONTENT.BODY like '%<ac:structured-macro ac:name="sql"%');

  
  Results:

  

  
  

• For the sql-query macro, execute the following query:
  

  Select CONTENT.Title, CONTENT.SpaceID from CONTENT, BODYCONTENT
  WHERE PREVVER IS NULL AND (CONTENT.CONTENTTYPE = 'PAGE' OR CONTENT.CONTENTTYPE = 'BLOGPOST')
  AND CONTENT.CONTENTID = BODYCONTENT.CONTENTID
  AND (BODYCONTENT.BODY like '%<ac:structured-macro ac:name="sql-query"%');

  
  Results:

  

  
  

• For the sql-file macro, execute the following query:
  

  Select CONTENT.Title, CONTENT.SpaceID from CONTENT, BODYCONTENT
  WHERE PREVVER IS NULL AND (CONTENT.CONTENTTYPE = 'PAGE' OR CONTENT.CONTENTTYPE = 'BLOGPOST')
   AND CONTENT.CONTENTID = BODYCONTENT.CONTENTID
   AND (BODYCONTENT.BODY like '%<ac:structured-macro ac:name="sql-file"%');

  
  Results: