How to list database tables or columns by partial name in Jira
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Admins may want to find database tables by a partial name or tables based on column names or partial column names.
This article shares a way to achieve this on Postgres, though table and column names don't change from a database to another, so the result fro Postgres would be the same as if in other databases. Customers' DBA teams may interpret and translate the queries here to their particular databases if needed.
All apps in Jira (including some components in Jira that were once plugins) have the AO_ prefix. This article should be helpful to identify which prefixes are from which apps:
List of Jira Data Center AO table names and vendors
Solution
List tables by partial name
This query will list all tables based on a partial table name:
1
2
3
4
5
6
7
select
table_name
from
information_schema.tables
where
table_schema = 'public'
and lower(table_name) like lower('%%');Sample output for '%link%':
1
2
3
4
5
6
7
8
9
10
11
12
13
14
table_name
--------------------------------
AO_38321B_CUSTOM_CONTENT_LINK
AO_563AEE_MEDIA_LINK_ENTITY
AO_5FB9D7_AOHIP_CHAT_LINK
issuelink
issuelinktype
remotelink
AO_D9132D_DEP_ISSUE_LINK_TYPES
AO_D9132D_SCENARIO_ISSUE_LINKS
AO_A415DF_AOEXTENSION_LINK
AO_6FF49D_LINKED_MEDIA
AO_589059_LEGACY_RULE_LINK
(11 rows)(some sample AO tables also listed from installed apps in the instance used on the query)
List tables by partial column name
It's also possible to list all tables and columns by partial table and column name, too:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/* Based on https://dataedo.com/kb/query/postgresql/find-tables-with-specific-column-name */
select
t.table_name, c.column_name
from
information_schema.tables t
inner join
information_schema.columns c
on c.table_name = t.table_name
and c.table_schema = t.table_schema
where
lower(t.table_name) like lower('%%')
and lower(c.column_name) like lower('%%')
and t.table_schema not in ('information_schema', 'pg_catalog')
and t.table_type = 'BASE TABLE'
order by
t.table_name, c.column_name;Sample output for table name '%AO_589059%' (Automation for Jira) and column name '%component%':
1
2
3
4
5
6
7
8
9
10
table_name | column_name
-------------------------------+--------------------------------
AO_589059_AUDIT_ITEM_ASC_ITEM | AUDIT_ITEM_COMPONENT_CHANGE_ID
AO_589059_AUDIT_ITEM_CGE_ITEM | AUDIT_ITEM_COMPONENT_CHANGE_ID
AO_589059_AUDIT_ITEM_COMP_CGE | COMPONENT
AO_589059_AUDIT_ITEM_COMP_CGE | COMPONENT_ID
AO_589059_AUDIT_ITEM_COMP_CGE | COMPONENT_NAME_KEY
AO_589059_RULE_CFG_COMPONENT | COMPONENT
AO_589059_RULE_CFG_COMPONENT | PARENT_CFG_COMPONENT_ID
(7 rows)Listing Sequences by partial name
A similar approach can be used to list Sequences by partial name:
1
2
3
4
5
6
7
select
sequence_name
from
information_schema.sequences
where
sequence_schema = 'public'
and lower(sequence_name) like lower('%%');Was this helpful?