How to find pages that uses a specific data source profile in different SQL macros

This article explains how to find pages that uses a specific data source profile in different SQL macros for a Confluence instance. The macros involved are SQL, SQL query and SQL file.

Instructions

Use the following query to get information about pages that use a specific data source profile:

SELECT c.contentid, c.contenttype, 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">Profile_name%';

Replace Profile_name in the query with a data source profile name from your Confluence instance.

The output of the above query is as shown:

This example is from MySQL database. You may have to tweak the query to use it in your database.