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 and 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 One          PROJ                Temp1            temp1            
Project Two          PT                  GitHook          githook          
~internal1           ~INTERNAL1          PersonalRepo     personalrepo     

Where

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

If you would like to find out when a project or repository was created, you can use the queries below for Postgres and MSSQL databases:

• Postgres 

  select
  	p."name" ,
  	p.project_key ,
  	a."ACTION" ,
  	cast(to_timestamp(a."ENTITY_TIMESTAMP" / 1000) as date) creation_date ,
  	a."USER_NAME"
  from
  	"AO_C77861_AUDIT_ENTITY" a ,
  	"project" p
  where
  	a."PRIMARY_RESOURCE_ID" = cast(p.id as varchar(255))
  	and "ACTION" in ('Project created', 'Repository created')
  	and cast(to_timestamp(a."ENTITY_TIMESTAMP" / 1000) as date) > '2023-12-31';

• MSSQL 

  MSSQL

  select

  a.PRIMARY_RESOURCE_ID ,

  a.ID ,

  p.name ,

  p.project_key ,

  a.USER_ID ,

  a.USER_NAME,

  a.ACTION,

  CAST(DATEADD(SECOND, a.ENTITY_TIMESTAMP / 1000

  , '1970/1/1') AS DATE) creation_date

  from

  AO_C77861_AUDIT_ENTITY a

  join project p on

  a.PRIMARY_RESOURCE_ID = p.id

  where

  CAST(DATEADD(SECOND, a.ENTITY_TIMESTAMP / 1000

  , '1970/1/1') AS DATE) > '2023-12-30'

  and a.[ACTION] in ('Project created', 'Repository created')

  order by

  a.ENTITY_TIMESTAMP desc;