Differences

This shows you the differences between two versions of the page.

--- 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.
+=== 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 ===
@@ Line 37: / Line 59: @@
 </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>

PawelWiki

User Tools

Site Tools

Differences

Page Tools