Useful SQL queries
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Plan
Plans with the most branches
1
2
3
4
5
6
7
SELECT p.full_key, p.title, COUNT(b.build_id)
FROM build p
JOIN build b ON p.build_id = b.master_id
WHERE p.master_id IS NULL
AND p.build_type = 'CHAIN'
GROUP BY p.full_key, p.title
ORDER BY 3 DESCArtifact sizes per deployment project
1
2
3
4
5
6
SELECT dp.name, sum(dvia.artifact_size)/1024 AS artifacts_size
FROM deployment_project dp
JOIN deployment_version dv ON (dp.deployment_project_id = dv.project_id)
JOIN deployment_version_item dvi ON (dv.deployment_version_id = dvi.deployment_version_id)
JOIN deployment_version_item_ba dvia ON (dvi.deployment_version_item_id = dvia.version_bam_artifact_item_id)
GROUP BY dp.nameBiggest artifacts in given deplopyment project
1
2
3
4
5
6
7
8
SELECT dp.name, dvia.artifact_id, dvia.artifact_size/1024 AS artifacts_size
FROM deployment_project dp
JOIN deployment_version dv ON (dp.deployment_project_id = dv.project_id)
JOIN deployment_version_item dvi ON (dv.deployment_version_id = dvi.deployment_version_id)
JOIN deployment_version_item_ba dvia ON (dvi.deployment_version_item_id = dvia.version_bam_artifact_item_id)
WHERE dp.name='DEP'
ORDER BY dvia.artifact_size
LIMIT 100Updated on April 8, 2025
Was this helpful?
Still need help?
The Atlassian Community is here for you.