===== 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 ===

<code java>
select * from sys.tables
</code>

=== Find all columns in a database ===

<code java>
select * from sys.columns
</code>

=== Find all tables & columns in a database ===

<code java>
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
</code>

=== Find user abc ===

<code java>
select * from cwd_user where lower_user_name = 'abc'
</code> 

=== Query on groups ===

<code java>
SELECT * from cwd_group
</code>

=== Query on jira directories ===

<code java>
select * from cwd_directory
</code>

=== Select all but 3 users from Jira Internal Directory  ===

<code java>
select * from cwd_user where directory_id = 1 and (user_name != 'abc' and user_name != 'cde' and user_name != 'sysadmin');
</code>

=== Disable user directory  ===

<code java>
select id, directory_name, active from cwd_directory;
</code>
Then you want to use this information to disable the directory
<code java>
update cwd_directory set active=0 where id=123456;
</code>


=== To resolve Missing filter in Agile board  ===
<code java>
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;
</code>

=== Get users in a group  ===

<code java>
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;
</code>

==== Get filternames in a dashboard ====

<code java>
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
</code>