Purpose

There are limitations for the default Lucene search algorithm in Confluence.

These are a few examples of the limitation :

• URL links are not searchable:  CONFSERVER-27503 - Getting issue details... STATUS
• Parts of a string are not searchable:  CONFSERVER-33996 - Getting issue details... STATUS  , CONFSERVER-36770 - Getting issue details... STATUS
  
  

There are times when you really need to search for the pages that contain the keywords.

For example, a user inserted an external URL into numerous pages, but as time goes by, those URLs might point to a dead link as there might be some changes in the subdomain/URL path.

Solution

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 ID referred to in the Confluence UI as the page ID, content type, page/blog title, and the Space Key.

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 '%<INSERT_KEYWORD_HERE>%';

SELECT c.CONTENTID, c.CONTENTTYPE, c.TITLE, s.SPACEKEY
FROM dbo.CONTENT c
JOIN dbo.BODYCONTENT bc
	ON c.CONTENTID = bc.CONTENTID
JOIN dbo.SPACES s
	ON c.SPACEID = s.SPACEID
WHERE c.PREVVER IS NULL
	AND c.CONTENTTYPE IN ('PAGE', 'BLOGPOST')
	AND bc.BODY LIKE '%<INSERT_KEYWORD_HERE>%';

See Also

How to find all pages and spaces that use a specific macro via SQL