How to get all Space Names, Keys and Homepage URLs from DB
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
This process requires the use of direct database manipulation and is not part of Confluence's intended functionality. As such, this process is not covered under the Atlassian Support Offerings and the information on this page is provided as-is. It should be thoroughly tested in a development or staging environment before implementing any changes in your production instance.
Summary
Sometimes it might be necessary to get a list of all Spaces, Space Key and home page URLs that exist in the instance. This information can be pulled directly from the database.
Solution
All space information is stored in Spaces Table. Home page is just a normal Confluence page, so it is simply referenced by PAGE ID in spaces table. This information can be turned into URL with concat function and Confluence PageID URL Format (See The Differences Between Various URL Formats for a Confluence Page).
Below query will give you Space Name, Space Key and home page URL
SELECT s.spacename, s.spacekey, CONCAT('https://confluence.example.com/pages/viewpage.action?pageId=', s.homepage)
FROM spaces AS s;
Replace https://confluence.example.com with your Confluence Base URL