It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Report of all active Jira projects and project leads


What query can I use to give me a list of all active Jira projects with the project leads. 

Thank you :)

3 answers

1 accepted

0 votes
Answer accepted

Hey Keshni,

If you have access to Jira database you could pull data using following SQL Queries

  • The first query gives project key as well as number of issues in it along with project lead
  • While the second query gives last updated with project key and project lead
select p.pkey,p.pcounter AS number_of_stories,p.LEAD from project p ORDER BY pcounter ASC;
SELECT DISTINCT p.pkey,p.LEAD,MAX(i.UPDATED) as "Last Updated"
FROM jiraissue i
INNER JOIN project p
GROUP BY p.pkey,p.LEAD


Hope this helps you get started



Chander Inguva

Hi Chander

You seem quite able with SQL, where I am not, and therefore I hope you can help me out.

I need to get the following from our MSSQL DB.

A list of all projects, with:

Last issue updated date

And the user who updated it

Project Lead

Project Lead Email


I hope you can help me out.


Hi @Chander Inguva ,

Thank you so much for this!! It helped...

How can I can the project name rather than the project key?

Just add p.pname Keshni.


Glad i could help you



Thank you :)

Good luck Keshni 

Hi @Chander Inguva 


I need to extract all project info from Jira database like below using SQL query please help.


Project Key

Project Description

Project Lead

Project Lead Email

Project Category

Project URL



Hey Srikanth,


Try this SQL

select p.pkey as [Project Key], p.DESCRIPTION as [Project Description], p.LEAD as [Project Lead], cu.email_address as [Project Lead Email], pc.cname as [Project Category], p.URL as [Project URL] from project p
JOIN nodeassociation na ON na.SOURCE_NODE_ID = p.ID
JOIN projectcategory pc ON na.SINK_NODE_ID = pc.ID
JOIN cwd_user cu ON cu.user_name = p.LEAD
where na.SINK_NODE_ENTITY ='ProjectCategory'


Hope this helps


Chander Inguva 

@Chander Inguva  That saved my day thanks for the help.

Appreciate it.




Glad i could help you @SreeKanth 




i needed this information too, so i just queried it from the database (mysql)

SELECT p.pname, p.pkey, u.display_name, u.email_address FROM jiradb.project p, jiradb.cwd_user u where lower(u.lower_user_name) = lower(p.lead);

this will give you the projects (without projects where the lead is no longer in jira) 

Suggest an answer

Log in or Sign up to answer

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you