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:
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:
It is recommended to:
Test the above queries in a test environment. The time taken to get the results is based on the number of pages where the macro has been used.
Run the query during off-business hours to reduce any impact caused by the query's run time.
Queries have to be adjusted with syntax in databases.