How to find pages where specific "web links" are present in Confluence Data Center
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および Crucible は除く
要約
This KB provides different ways to fetch a list of pages which contains a specific link.
データベースの変更を行う前に必ずデータをバックアップしてください。可能であれば、まずステージング サーバーで、すべての変更、挿入、更新、または削除の SQL コマンドをテストしてください。
ソリューション
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>';