This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
jira_sql_queries [2017/07/13 10:40] pawel created |
jira_sql_queries [2019/06/11 13:43] (current) shabnam |
||
---|---|---|---|
Line 2: | Line 2: | ||
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. | 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 | ||
+ | </ | ||
+ | |||
+ | === Find all columns in a database === | ||
+ | |||
+ | <code java> | ||
+ | select * from sys.columns | ||
+ | </ | ||
+ | |||
+ | === 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 | ||
+ | </ | ||
=== Find user abc === | === Find user abc === | ||
Line 37: | Line 59: | ||
</ | </ | ||
+ | |||
+ | === To resolve Missing filter in Agile board === | ||
+ | <code java> | ||
+ | SELECT * | ||
+ | FROM AO_60DB71_RAPIDVIEW | ||
+ | WHERE SAVED_FILTER_ID=55555; | ||
+ | |||
+ | //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 === | ||
+ | |||
+ | <code java> | ||
+ | select distinct(U.display_name), | ||
+ | from cwd_membership G, cwd_user U | ||
+ | where G.parent_name = << | ||
+ | and G.lower_child_name = U.lower_user_name | ||
+ | and U.active=' | ||
+ | order by U.display_name; | ||
+ | </ | ||
+ | |||
+ | ==== Get filternames in a dashboard ==== | ||
+ | |||
+ | <code java> | ||
+ | SELECT pp.id, pp.username, | ||
+ | JOIN portletconfiguration pc on pp.id = pc.portalpage | ||
+ | JOIN gadgetuserpreference gup on pc.ID = gup.portletconfiguration | ||
+ | JOIN searchrequest s on gup.userprefvalue=s.filtername | ||
+ | </ |