How to get usage information about the SQL for Confluence app

This article explains how to get a list of Confluence pages and the respective spaces where the SQL for Confluence app is used.

Instructions

1. Log into Confluence, navigate to the required page where you want to display the usage information.

2. Edit the page, and, enter {sql or search for the SQL for Confluence macro.

   You must know the Data source profile name that can be used to run queries given in the following instructions.

   If you do not know which profile name can be used, contact your Confluence Administrator before proceeding.

3. Edit the macro and select the Data source profile.
   

4. In your SQL profile table, there is a number associated with the table mentioned in the query. Replace XXXX with that number and run the given SQL query to get the list of database profiles created in your Confluence instance.

   select profile_name from AO_xxxxx_SQLPROFILES;

5. Click Save settings.
   

6. Save your Confluence page.
   

7. Replace Mysql_confluence with your database profile name in the following query. Run the query for each database profile name (taken from the query results above) to get the list of spaces.

   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%';

8. Click Save settings to save the query.

   
9. Save your Confluence page.
   

10. Run the following query to list all the pages in the space (taken from the query results above) that uses the macro.

    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"%');
    

11. Click Save settings.
    
12. Publish the page to view the entire list of spaces and pages where the macro is used.