How to find all pages that use SQL for Confluence (Pro Edition) 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

Option 1:

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

Open

Open

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

Option 2:

Usage information using SQL queries:

As the SQL for Confluence has three macros, the query must be executed 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: