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. All SQL queries are for Postgres and might need to be modified to work on other database engines.
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>';