How to display the list of pages in confluence containing the SQL macro

This article explains how to get the list of pages in confluence containing the SQL macro using the app SQL for Confluence (Pro Edition)

Instructions

Follow the steps:

1. Log into the Confluence instance.

2. Go to the required confluence page and edit the same.

3. Add SQL macro and go to the Edit option to open the settings.

4. Run below SQL query to get the list of DB profiles created in your Confluence instance. In your SQL profile table, there will be number for this table so replace XXXX value with the number.

   select profile_name from AO_xxxxx_SQLPROFILES;

5. After getting the list of Database profiles from the above query, please replace the database profile name in place of Mysql_confluence in the below query to get the list of space details. Execute the below query for each of the database profile names.

   SELECT c.title, s.spacekey 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="dataSource">Mysql_confluence%';

6. This SQL query will list all the pages containing SQL macro in your confluence space.

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