Purpose

Whenever you have too many projects and too many repositories configured in the instance, having a better picture of how many of each you have can become challenging.

The purpose of this article is to help anybody that needs to retrieve a list of projects and repositories, along with personal repositories, directly from the database used by Bitbucket Server.

Solution

• Connect to the database used by Bitbucket Server using the database vendor supplied tool or maybe using a client like DBVisualizer.

• Run the following SQL query:

  SELECT 
         prj.name AS "Project / User Name"
       , prj.project_key AS "Project / User Key"
       , rep.name AS "Repository Name"
       , rep.slug AS "Repository Slug"
  FROM repository rep 
  INNER JOIN project prj ON rep.project_id = prj.id
  ORDER BY prj.name, rep.name

• Sample result:

  Project / User Name  Project / User Key  Repository Name  Repository Slug  
  -------------------  ------------------  ---------------  ---------------  
  Project              PROJ                Temp1            temp1            
  Project Two          PT                  GitHook          githook          
  ~internal1           ~INTERNAL1          PersonalRepo     personalrepo     

  Where

  • Project Project One has one repositories, named Temp1.
  • Project Project Two has one repository, named GitHook.
  • User internal1 has a personal repository, named PersonalRepo.