User Tools

Site Tools


jira_sql_queries

Jira SQL Queries

Below is the list of SQL queries to perform tasks directly on the database. Please note that you should always shut down Jira & backup your database before performing any of these queries.

Find all tables in a database

select * from sys.tables

Find all columns in a database

select * from sys.columns

Find all tables & columns in a database

SELECT t.name AS table_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
order by table_name

Find user abc

select * from cwd_user where lower_user_name = 'abc'

Query on groups

SELECT * from cwd_group

Query on jira directories

select * from cwd_directory

Select all but 3 users from Jira Internal Directory

select * from cwd_user where directory_id = 1 and (user_name != 'abc' and user_name != 'cde' and user_name != 'sysadmin');

Disable user directory

select id, directory_name, active from cwd_directory;

Then you want to use this information to disable the directory

update cwd_directory set active=0 where id=123456;

To resolve Missing filter in Agile board

SELECT *
FROM AO_60DB71_RAPIDVIEW
WHERE SAVED_FILTER_ID=55555; //<- this is the filter id mentioned in the error//
 
//Take a note of the ID field value (eg.: 999), and delete the reference records from the below tables as below://
 
DELETE
FROM AO_60DB71_BOARDADMINS
WHERE RAPID_VIEW_ID=999;
 
DELETE
FROM AO_60DB71_DETAILVIEWFIELD
WHERE RAPID_VIEW_ID=999;
 
DELETE
FROM AO_60DB71_ESTIMATESTATISTIC
WHERE RAPID_VIEW_ID=999;
 
DELETE
FROM AO_60DB71_QUICKFILTER
WHERE RAPID_VIEW_ID=999;
 
DELETE
FROM AO_60DB71_STATSFIELD
WHERE RAPID_VIEW_ID=999;
 
DELETE
FROM AO_60DB71_SWIMLANE
WHERE RAPID_VIEW_ID=999;
 
DELETE
FROM AO_60DB71_TRACKINGSTATISTIC
WHERE RAPID_VIEW_ID=999;
 
DELETE
FROM AO_60DB71_WORKINGDAYS
WHERE RAPID_VIEW_ID=999;
 
DELETE
FROM AO_60DB71_CARDCOLOR
WHERE RAPID_VIEW_ID=999;
 
DELETE
FROM AO_60DB71_SUBQUERY
WHERE RAPID_VIEW_ID=999;
 
//Take a note of the IDs from the table AO_60DB71_COLUMN//
SELECT * FROM AO_60DB71_COLUMN
WHERE RAPID_VIEW_ID=999;
 
Example results: 1,11,111.
 
//Delete these references in table: AO_60DB71_COLUMNSTATUS//
DELETE FROM AO_60DB71_COLUMNSTATUS
WHERE COLUMN_ID IN (1,11,111);
 
//Then delete the primary record of AO_60DB71_COLUMN table//
DELETE FROM AO_60DB71_COLUMN
WHERE RAPID_VIEW_ID=999;
 
//Now finally delete the record in primary table://
DELETE 
FROM AO_60DB71_RAPIDVIEW
WHERE SAVED_FILTER_ID=55555;

Get users in a group

select distinct(U.display_name), U.lower_user_name, U.email_address, G.parent_name 
from cwd_membership G, cwd_user U 
where G.parent_name = <<group_name>>
and G.lower_child_name = U.lower_user_name
and U.active='1'
order by U.display_name;

Get filternames in a dashboard

SELECT pp.id, pp.username, pp.pagename, s.filtername, s.id, gup.USERPREFVALUE FROM portalpage pp
JOIN portletconfiguration pc on pp.id = pc.portalpage
JOIN gadgetuserpreference gup on pc.ID = gup.portletconfiguration
JOIN searchrequest s on gup.userprefvalue=s.filtername
jira_sql_queries.txt · Last modified: 2019/06/11 13:43 by shabnam