How to list all Jira issue-to-issue links in a specific project from the database in Jira Data Center
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
Summary
As an admin, you might need a list of all issue links for a specific project. This could be for several reasons, including post-migration audits. This article outlines how to identify all issue links from a specific project in the database.
Environment
Any Jira version.
Solution
The following database tables hold information related to issue links:
Table name | Purpose |
|---|---|
issuelink | The link between Jira issues within the same instance |
issuelinktype | The type of link (related, blocked, caused, etc) |
remotelink | The link between Jira issues and entities from external instances (eg. link to remote Jira issue, link to Confluence page, etc) |
The following SQL queries list issue links based on various criteria.
To identify all issue links within the same instance for issues in a specific project:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--All inward links for issues in a specific project, along with their corresponding issue ID. Replace <pkey> with the project key of your choice
SELECT IL.DESTINATION, CONCAT(P1.PKEY, '-', JI1.ISSUENUM), ILT.INWARD, CONCAT(P2.PKEY, '-', JI2.ISSUENUM), IL.SOURCE
FROM ISSUELINK IL
LEFT JOIN ISSUELINKTYPE ILT ON IL.LINKTYPE = ILT.ID
LEFT JOIN JIRAISSUE JI1 ON JI1.ID = IL.DESTINATION
LEFT JOIN JIRAISSUE JI2 ON JI2.ID = IL.SOURCE
LEFT JOIN PROJECT P1 ON P1.ID = JI1.PROJECT
LEFT JOIN PROJECT P2 ON P2.ID = JI2.PROJECT
WHERE P1.PKEY = '<pkey>'
ORDER BY JI1.ID ASC;
--All outward links for issues in a specific project, along with their corresponding issue ID. Replace <pkey> with the project key of your choice
SELECT IL.SOURCE, CONCAT(P1.PKEY, '-', JI1.ISSUENUM), ILT.OUTWARD, CONCAT(P2.PKEY, '-', JI2.ISSUENUM),IL.DESTINATION
FROM ISSUELINK IL
LEFT JOIN ISSUELINKTYPE ILT ON IL.LINKTYPE = ILT.ID
LEFT JOIN JIRAISSUE JI1 ON JI1.ID = IL.SOURCE
LEFT JOIN JIRAISSUE JI2 ON JI2.ID = IL.DESTINATION
LEFT JOIN PROJECT P1 ON P1.ID = JI1.PROJECT
LEFT JOIN PROJECT P2 ON P2.ID = JI2.PROJECT
WHERE P1.PKEY = '<pkey>'
ORDER BY JI1.ID ASC;To identify all remote issue links for issues in a specific project:
1
2
3
4
5
6
7
--All remote Jira-Jira issue links for issues in a specific project, along with their corresponding issue ID. Replace <pkey> with the project key of your choice
SELECT RL.ISSUEID, CONCAT(P.PKEY, '-', JI.ISSUENUM), RL.RELATIONSHIP, RL.TITLE, RL.URL
FROM REMOTELINK RL
LEFT JOIN JIRAISSUE JI ON JI.ID = RL.ISSUEID
LEFT JOIN PROJECT P ON P.ID = JI.PROJECT
--WHERE P.PKEY = '<pkey>'
ORDER BY JI.ID ASC;To find dependencies between two projects:
1
2
3
4
5
6
SELECT i1.pkey, i2.pkey
FROM issuelink l
JOIN jiraissue i1 ON i1.id = l.source
JOIN jiraissue i2 ON i2.id = l.destination
WHERE i1.project = (SELECT id FROM project WHERE pkey = '<pkey_A>')
AND i2.project = (SELECT id FROM project WHERE pkey = '<pkey_B>');ℹ️ Replace '<pkey_A>' and '<pkey_B>' with the actual project keys. This query will return pairs of issue keys where an issue in project A is linked to an issue in project B.
ℹ️ All queries are written for PostgreSQL. You may have to re-write it for your specific database.
Was this helpful?