How to find pages where specific "web links" are present in Confluence Data Center
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
This KB provides different ways to fetch a list of pages which contains a specific link.
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Solution
Below is the SQL statement to get a list of pages (all status) which contains a specific link. Replace <weblink> for the link you are interested in.
select c.Contenttype,c.title,c.content_status,s.spacename from content c join bodycontent b on b.contentid = c.contentid
join spaces s on c.spaceid = s.spaceid
where b.body like '%<weblink>%';
The following SQL statement gets a list of pages which contain a specific link and the page status is "current". As in previous statement, please replace <weblink> for the link you are interested in.
select c.Contenttype,c.title,c.content_status,s.spacename from content c join bodycontent b on b.contentid = c.contentid
join spaces s on c.spaceid = s.spaceid
where b.body like '%<weblink>%'
AND c.content_status='current';
select c.Contenttype,c.title,c.content_status,s.spacename from content c join bodycontent b on b.contentid = c.contentid
join spaces s on c.spaceid = s.spaceid
where b.body like '%<weblink>%'
AND c.PREVVER IS NULL; Finally, this SQL statement helps you to get a list of pages which contain a specific link and the page status is "current" in a particular space. In this occasion, please replace <weblink> for the link you are interested in and <spacename> with the actual space name you want to search for.
select c.Contenttype,c.title,c.content_status,s.spacename from content c join bodycontent b on b.contentid = c.contentid
join spaces s on c.spaceid = s.spaceid
where b.body like '%<weblink>%'
AND c.content_status='current'
AND s.spacename ='<spacename>';