How to perform a Confluence site search for keywords and links through the database
There are limitations for the default Lucene search algorithm in Confluence.
These are a few examples of the limitation :
- URL links are not searchable : - CONF-27503Getting issue details... STATUS
- Parts of a string are not searchable : - CONF-33996Getting issue details... STATUS , - CONF-36770Getting issue details... STATUS
There are times when you really need to search for the pages that contain the keywords.
For an example, an external URL was inserted to numerous pages but as time goes by, those URL might point to a dead link as there might be some changes in the subdomain/URL path.
To search for these contents, run the SQL query below on your Confluence database. Replace the <INSERT_KEYWORD_HERE> with your keyword. The % symbol represents a wildcard search.
The SQL results will return the content type, title along with the space details including the spacestatus (either CURRENT or ARCHIVED). If a space is Archived, it won't be searchable in Confluence's User Interface.
select c.CONTENTTYPE,c.TITLE, s.SPACENAME, s.SPACEKEY, s.SPACETYPE, s.SPACESTATUS from content c join spaces s on c.SPACEID=s.SPACEID where CONTENTID in (select CONTENTID from bodycontent where BODY like '%<INSERT_KEYWORD_HERE>%')